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.
I have a table designed vertically for dimensions.
Product Table -
Product Dimension | Product Category |
A | Fruit |
B | Vegetable |
C | Diary |
SomePrimaryID | Product Dimension | Value |
1 | A | 1 |
2 | A | 2 |
3 | B | 3 |
1 | B | 4 |
2 | B | 5 |
1 | C | 6 |
3 | C | 5 |
Now, I have a slicer on Product Dimension.
A
B
C
When user selects A and C together, I want magical DAX to do the following -
For A, the following data belongs to Product A as per table above.
SomePrimaryID | Product Dimension | Value |
1 | A | 1 |
2 | A | 2 |
For C, the following data belongs to Product C as per table above.
SomePrimaryID | Product Dimension | Value |
1 | C | 6 |
3 | C | 5 |
Now, I want only unique primary IDs to be considered between A and C to get SUM(Value).
Looking at above - unique primary ID between A and C is 1.
SomePrimaryID | Product Dimension | Value |
1 | A | 1 |
1 | C | 6 |
Therefore, Product Dimension A & C selected in the slicer should return the following in -
Product Dimension | Value |
A | 7 |
Total | 7 |
DAX I tried:
VAR ProdFruits =
CalculateTable (Values(SomePrimaryID), Filter(Products, Product = "Fruit")
Var ProdDiary =
CalculateTable (Values(SomePrimaryID), Filter(Products, Product = "Diary")
Var Intersection =
NATURALINNERJOIN(ProdFruits, ProdDiary)
RETURN
CALCULATE (SUMX(Intersection, SUM(Value)), Product Dimension = "Fruit")
For some reason, I am not able to figure out where am i going wrong and why its not working!!
I hope I was clear enough in explaining the problem examples above. Thanks in advance.
Link to sample PBIX file - SAMPLE PBIX FILE
Solved! Go to Solution.
Thanks Ricardo, I tried the DAX above and doesnt seem to give me desired output :(.
When I replaced the measure with your suggested DAX, I get the following -
Product Dimension | CalculatedValue |
A | 1 |
C | 6 |
Total | 7 |
However, what i want to see is the following -
Product Dimension | CalculatedValue |
A | 7 |
C | 7 |
Total | 7 |
I believe it is very close to the solution, not sure what other manipulations in DAX syntax can be done to acheive above?
Your help is much appreciated.
Thanks.
Hi @Anonymous ,
Try this code:
Thanks Ricardo, I tried the DAX above and doesnt seem to give me desired output :(.
When I replaced the measure with your suggested DAX, I get the following -
Product Dimension | CalculatedValue |
A | 1 |
C | 6 |
Total | 7 |
However, what i want to see is the following -
Product Dimension | CalculatedValue |
A | 7 |
C | 7 |
Total | 7 |
I believe it is very close to the solution, not sure what other manipulations in DAX syntax can be done to acheive above?
Your help is much appreciated.
Thanks.
Find the solution attached. But note that measures where ALLSELECTED is used should never be used in ITERATORS. This is of utmost importance. If you ignore this rule, your measures will be WRONG. Simply. And you won't be able to understand why. You'll need to read a lot about how ALLSELECTED works and there's a lot to understand.
Best
D
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 |
---|---|
55 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
78 | |
63 | |
46 | |
17 | |
12 |