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.
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
Solved! Go to Solution.
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."
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.
@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
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."
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |