cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
usman2338 Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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:

image.png

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Super User
Super User

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

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:

image.png

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 469 members 5,179 guests
Please welcome our newest community members: