Forum Discussion
How is a table value handled/processed in the expression of CALCULATE?
I'm hoping someone can help me understand the logic of how the expression in a CALCULATE function works when there's only inserted a table column in it. Beneath I've created an example:
Fact table:
Type: Value:
A 1
B 5
C 2
Slicer:
A slicer that filters out "type = C".
Measures:
Following measures is the source of my question:
measure1 =
var ValuesColumn = VALUES('Table'[Value])
Return
CALCULATE(COUNTA('Table'[Value];ValuesColumn)measure2 =
var ValuesColumn = VALUES('Table'[Value])
Return
CALCULATE(COUNTA('Table'[Value];FILTER('Table';'Table'[Value] in ValuesColumn)
I know this is a very simplified setup but my question is basically how these 2 approaches are handled/processed? And do they even yield a different result?
In your example with just a single table both measures will returnt the same value, but the filter context of both is slightly different so in a more complicated scenario the results could be different.
Basically in both measures the variable ValuesColumn will return a single column table with a single value of "2"
measure1 says to count the values in the Table[Value] column where the value in Table[Value] is "2" (from the variable)
measure2 also says to count the values in the Table[Value column, but the filter this time is on the entire "Table" table (so both the Type and Value columns) so it is effectively filtered for Type = "C" and Value = 2.
In this single table example both measure1 and measure2 will return the same value, but the extra columns in the filter condition in measure2 could change things in more complicated scenarios.
3 Replies
- d_gosbell
Super User
In your example with just a single table both measures will returnt the same value, but the filter context of both is slightly different so in a more complicated scenario the results could be different.
Basically in both measures the variable ValuesColumn will return a single column table with a single value of "2"
measure1 says to count the values in the Table[Value] column where the value in Table[Value] is "2" (from the variable)
measure2 also says to count the values in the Table[Value column, but the filter this time is on the entire "Table" table (so both the Type and Value columns) so it is effectively filtered for Type = "C" and Value = 2.
In this single table example both measure1 and measure2 will return the same value, but the extra columns in the filter condition in measure2 could change things in more complicated scenarios.
Thank you for the explanation and examples d_gosbell
Hi Anonymous ,
I'd like to suggest you take a look at following blog about variable use in Dax calculation:
Variables in DAX
Regards,Xiaoxin Sheng