Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |