Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a large data Table 1 in the following form:
ID | Product | Type | Contribution Per Type | Measure 1 | Measure 2 | |||||
1 | Shoes | A | 100 | 20 | 30 | |||||
1 | Shoes | B | 20 | 20 | 30 | |||||
1 | Shoes | C | 30 | 20 | 30 | |||||
2 | Pants | A | 40 | 10 | 50 | |||||
2 | Pants | C | 50 | 10 | 50 |
I have a slider with filters for fields Type, subtype, from Table 2, showing the options in boxes (multiple selection):
Type Subtype
A
AA
AB
B
BA
BC
C
CA
CB
Table 1, and Table 2 are related by the Type field. The desired output table I would like to generate is:
Project Name Total Contribution per type Value 1 Value 2
Shoes 150 20 30
Pants 90 10 50
Total 240 30 80
The DAX measure I have for Value 1 is = CALCULATE (MAX(Table 1[Value 1]), ALLEXCEPT(Table 2, Table 2[Type], Table2[Subtype]))
The result for Value 1 is correct, row by row, and total, if all filters are selected. However, if I select in the filter Type B in the slider, the values per row are correct, but the total remains = 30. If I select B, the total for measure 1 should be 20.
How can I get a measure for Value 1 that shows the correct total when a filter is selected?
Thank you,
Solved! Go to Solution.
Hi @Danny2020
You can use the following two measures:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Hi @Danny2020
You can use the following two measures:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
@Danny2020 , Try like
Sumx(Values(Table2[[Type]) ,CALCULATE (MAX(Table 1[Value 1])))
Hi @amitchandak,
Unfortunately the measure is overcounting the total for each project name, and also the general total.
Any ideas?
Thank you,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |