topic Re: Dax measure impacting external filter context not able to figure out why it is happening in DAX Commands and Tips

Dax measure impacting external filter context not able to figure out why it is happening

dhrubojtg — Thu, 18 Mar 2021 12:05:49 GMT

Dear Dax Gurus ,

Thank you if any guru can share some light to uncover this problem . .

I understand that External filters as coming from GUI report can imact the measure value .
However in this case it appears that reverse is happending in this case

When I use the measure as below I see that all my subategories are getting messed up that is incorrect subcategories get displayed under respective categories. I would be thankful if you can explain for each of the measure .

In both the measure , same problem

Link to PBIX for investigation : https://www.dropbox.com/s/rmvqurnkwvxo6ob/ContosoDW2.pbix?dl=0
Measure used :

Measure: ComputerSalesWithoutFilters = CALCULATE(sum('Online Sales'[SalesAmount]),'Product Category'[Category]="Computers") ComputerSalesSame = CALCULATE(sum('Online Sales'[SalesAmount]),(All('Product')),'Product Category'[Category]="Computers")

Problem :
Subcatetories in the below matrix gets messed up when I use above measure , if i don't then subcategories are displayed correctly.

When first measure ComputerSalesWithoutFilters is used , then all computer subcategories appears in each of the categories.
When 2nd measure ComputerSalesSame is used all subcategories no matter if they match category, comes under each subcategories

Re: Dax measure impacting external filter context not able to figure out why it is happening

Anonymous — Thu, 18 Mar 2021 13:55:13 GMT

ALL( 'Product' ) under your CALCULATE simply removes all filters from Product. ALL in this context does not return a table as you might think. It REMOVES all filters from the table. Additionally, 'Product' in your measure is treated as an expanded table, so it removes also all filters from tables that are joined to 'Product' and are on the one-side of the relationship.

To understand the code one has to understand the theory of expanded tables in DAX. I'd start reading a book by Marco Russo and Alberto Ferrari - "The Definitive Guide to DAX."

Re: Dax measure impacting external filter context not able to figure out why it is happening

dhrubojtg — Thu, 18 Mar 2021 17:20:04 GMT

Anonymous
By that same token even in the first measure
There is one implicit All inserted by DAX for product category[category]column and on the one side we have subcategory table , So Same result should have com in below measure as well (First screenshot)
But in the first measure , i see only computer subcategory is being displayed across all category

Re: Dax measure impacting external filter context not able to figure out why it is happening

Anonymous — Sat, 20 Mar 2021 14:08:50 GMT

The easiest way to make it right is to liquidate the snowflake schema and flatten the two tables, Product Category and Product Subcategory, into one. With snowflakes one has to be careful when writing DAX. If you have a pure star schema, life is soooooo much simpler! As will be your DAX. Please bring categories and subcategories into one table and the issues will be gone, your DAX will be simpler and faster. What's more, you'll be able to create a hierarchy that will make it easier to pick fields that should be related and in a set arrangement.