Filtering a Table with multiple columns using single (multi-select) Slicer whilst applying AND logic

[BastiaanBrak]

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). 

Single slicer (AND) filter =

var _measure =

var _a = IF(ISFILTERED('Fruit reference list1'[Fruit]),CONCATENATEX('Fruit reference list1','Fruit reference list1'[Fruit]))

var _b = SELECTEDVALUE('Table'[ConcatenatedIngredients])

return CONTAINSSTRING(_b,_a)

return if (_measure,1,0)

This only works if one fruit has been selected or if more than one fruit following in consecutive order are selected (as per screenshot above) but not if the selection is otherwise. For example, if Banana and Damson are selected in slicer, two combinations should be returned but my current solution finds only one, omitting the 'Apple - Banana - Cherry - Damson' combination because I'm concatenating the selected values and then matching that against the concatenated full combination.

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

[Ashish_Mathur]

Hi,

RIght click on the first and the last column headings and select "Unpivot other columns".  Now try to build your visual.

[BastiaanBrak]

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. 

[CNENFRNL]

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

[CNENFRNL · Accepted solution]

Create a relationship between the slicer table and the fact table,

_ =
IF(
    COUNTROWS( 'Table (unpivoted)' )
        = COUNTROWS( ALLSELECTED( 'Fruit reference list1'[Fruit] ) ),
    ""
)

[BastiaanBrak]

@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

[jitendracs2004]

@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.

[CNENFRNL]

I agree on your good way to author a robust measure.