Difference in results when using FILTER keyword & syntax against direct filter

[usman2338]

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.

[TomMartens · Accepted 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

1. https://pbidax.wordpress.com/2016/05/22/simple-filter-in-dax-measures/
2. http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

Regards,

Tom