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.
hi there, am struggling to solve a filtering issue using DAX code and hoping someone might be able to advice.
To set the context: I've got a report with a table where each row represents a unique combination of 4 items (each presented in a different column) out of 33 possible items resulting in 40,920 rows (33 x 32 x 31 x 30) / (4 x 3 x 2 x 1), with each combination given a particular score.
To simplify this with a smaller table with 5 different fruits and therefore 5 different possible combinations:
What I would like to do is create a single (multi-select) slicer that can filter the four Ingredient columns at the same time such that only combinations are returned that include ALL the selected items in the slicer, i.e. apply AND logic.
For example:
I watched Alberto Ferrari's video here to see if his DAX code could be modified to my use case but tbh I can't get my head round whether it could or not. The DAX solution I am trying to make work is the following (modified from another post here on MPB Community). I have the following measure, which I add as a Visual level filter on the Table (i.e. Single slicer (AND) filter = 1).
Instead what I would like to do is not concatenate all the items selected but using CONTAINSSTRING check for each selected item if they are present in the respective combination and then return only those combinations in the table for which all are present.
Hence the question is: how do I modify my DAX formula above to achieve that?
A .pbix file can be downloaded here (until 21 July): https://filebin.net/yuv9xmv182cixzlf and the relevant page is called Single slicer (AND).
Hope that makes sense but happy to clarify further.
Thanks Bastiaan
Solved! Go to Solution.
Create a relationship between the slicer table and the fact table,
_ =
IF(
COUNTROWS( 'Table (unpivoted)' )
= COUNTROWS( ALLSELECTED( 'Fruit reference list1'[Fruit] ) ),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi,
RIght click on the first and the last column headings and select "Unpivot other columns". Now try to build your visual.
Not here to pick a fight but I really don't understand why people are kudo'ing this response - which
1) isn't a precise answer to the question
2) disregards that my data model already included the unpivoted table as part of the solution, as can be seen in the attached .pbix file
I asked specifically for advice on how to modify my initial DAX attempt; @CNENFRNL's solution does that whereas @Ashish_Mathur, unfortunately, doesn't.
Take it easy, my friend. Kudo or not, it's not a problem. I myself never mind it. Maybe those kudo-givers think they benefit from some unprecise solutions. Sometimes I'd provide precise solutions if I've enough time; sometimes only a few words as hint. From now and then, I also got inpired by some unprecise hints by others. It's not that unacceptable. Chacun son goût
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Create a relationship between the slicer table and the fact table,
_ =
IF(
COUNTROWS( 'Table (unpivoted)' )
= COUNTROWS( ALLSELECTED( 'Fruit reference list1'[Fruit] ) ),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL thanks so much, a really elegant solution!
Because I want to display the table as per original post I re-established the relationship between the unpivoted and the pivoted table in the data model. I also added an IF ISFILTERED clause to your DAX formula to ensure that all rows are displayed when no selection has been applied in the slicer.
.pbix with adapted solution here: https://file.io/MPsDpdxeqFDh
@BastiaanBrak I hope you still have this file. Actually I have the same kind of requirement and unable to crack it. Could you please share the same if possible.
I agree on your good way to author a robust measure.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |