如何在度量（DAX）中引用计算列中的单个值 [英] How to refer to a single value in a Calculated Column in a measure (DAX)

问题描述

我有一个表（Data_all），该表在一个表中计算daycount_ytd。

I have a table(Data_all) that calculates daycount_ytd in one table.

[Date] is in Date Format. 
[Fiscal Year] is just year. eg: 2016

Calculated Column
daycount_ytd=DATEDIFF("01/01/"&[Fiscal Year],Data_all[Date],day)+1

我正在尝试创建引用此计算列的度量

Im trying to create a measure that refers to this Calculated Column

Measure:
Amt_X Yield %:=[Amt X]/([Amt Y]/365* (Data_all[DayCount_YTD]))

我收到以下错误：Data_all [DayCount_YTD]引用值列表。
如何不使用聚合函数（例如：（总和，中位数））对表达式进行过滤以获取单个值？

I get the error that Data_all[DayCount_YTD] refers to a list of values. How do i filter the expression to get a single value without using a aggregation function eg:(sum, median)?

也许，还有另一种方法可以实现相同的计算吗？

Or perhaps, is there another way to achieve the same calculation?

推荐答案

您已经掌握了DAX的一个基本概念，一旦确定了如何处理它，该解决方案就会推广到各种情况。

You've arrived an a fundamental concept in DAX and once you've worked out how to deal with it then the solution generalises to loads of scenarios.

您不能只将列传递给DAX度量而不将其包装在其他内容中-通常是某种数学运算，也可以根据要执行的操作使用VALUES（）。

Basically you can't just pass columns into a DAX measure without wrapping them in something else - generally some kind of mathematical operation or you can use VALUES() depending on exactly what you are trying to do.

如果在带有日期作为行标签的PIVOT中使用此度量，将可以正常使用

This measure will work OK if you use it in a PIVOT with the date as a row label:

=
  SUM ( data_all[Amt X] )
   / (
      SUM ( data_all[Amt Y] ) / 365
      * MAX ( data_all[daycount_ytd] )
      )

但是，您会发现它的总和不正确，因为它是最新的。您需要的是一个对行进行迭代，然后执行计算以求和或平均每个项目的版本。有专门用于此目的的一整套DAX函数，例如SUMX，AVERAGEX等。您可以阅读有关它们的更多信息此处

However you will see it gives you an incorrect total as it is in the latest for the entire thing. What you need is a version that iterates over the rows and then performs a calculation to SUM or AVERAGE each item. There is a whole class of DAX functions dedicated to this such as SUMX, AVERAGEX etc. You can read more about them here

我尚不清楚我的总计应该是，但是以下度量将计算每天的值并将它们相加：

It's not totally clear to me what the maths behind your 'total' should be but the following measure calculates the value for each day and sums them together:

=
  SUMX(
      VALUES(data_all[date]), 
         SUM(data_all[Amt X]) / 
         (SUM(data_all[Amt Y]) / 365 * MAX(data_all[daycount_ytd]))
       )

这篇关于如何在度量（DAX）中引用计算列中的单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！