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.
Hi,
I would appreciate if someone could explain difference between following 2 DAX Expressions assuming some Filters such as dates, some items from a column, etc have been selected in Slicers and the data is being shown in those filters context. Only one table with some columns is being used.
Result = CALCULATE(COUNTROWS(Table), Table(Column) = "abc")
VS
Result = CALCULATE(COUNTROWS(Table), FILTER(Table, Table(Column) = "abc"))
When i use the 1st expression and create 3 measures for values "abc", "efg", "xyz" & show the results in multicard and select one of the item in a Slicer then the value(COUNTROWS) of other 2 items should become BLANK but they do not and show some count. However, the 2nd expression gives results as expected.
Solved! Go to Solution.
Hey,
this question touches the fundamentals of DAX, meaning the Evaluation Context, the Filter Context, and all that stuff ...
This filter expression
..., 'Table'[Column] = "abc" ...
is an abbreviation for this
..., FILTER(ALL('Table'[Column]),'Table'[Column] = "abc") ...
This means, that an existing implicit filter for the column "Column" (coming from a slicer) will be removed and will be replaced by the explicit filter from the DAX statement.
This replacedement will not happen, if you are using "..., FILTER(....) ..." syntax.
For this reason both solutions provide different results, even if they look pretty much similar.
Here is a little screenshot from a report that shows the above in action:
For a better understanding you might consider to read these blog posts by Jeffrey Wang (Product Owner of DAX engine @ Microsoft), please be warned - it can become mindboggling :-), especially the 2nd one
Regards,
Tom
Hey,
this question touches the fundamentals of DAX, meaning the Evaluation Context, the Filter Context, and all that stuff ...
This filter expression
..., 'Table'[Column] = "abc" ...
is an abbreviation for this
..., FILTER(ALL('Table'[Column]),'Table'[Column] = "abc") ...
This means, that an existing implicit filter for the column "Column" (coming from a slicer) will be removed and will be replaced by the explicit filter from the DAX statement.
This replacedement will not happen, if you are using "..., FILTER(....) ..." syntax.
For this reason both solutions provide different results, even if they look pretty much similar.
Here is a little screenshot from a report that shows the above in action:
For a better understanding you might consider to read these blog posts by Jeffrey Wang (Product Owner of DAX engine @ Microsoft), please be warned - it can become mindboggling :-), especially the 2nd one
Regards,
Tom
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |