Filter table based on slicer with multiselect for a column with combinations

[JFG123]

I am trying to filter the data table in power bi reports where if I select A and B in the drop-down, the data table in report view keeps all rows that have A AND B in them. Ie it would only keep the first row where the combination is A,B,C

[v-luwang-msft · Accepted solution]

Hi @JFG123 ，

The way Samarth_18 shared ,only  suitable for cases where the slicer selects only one value.When your slicer selects more than one value, an error will be reported.You could try the following ways.

Output:

Solutions:

Use the following dax to create new column:

first = LEFT(Data[Combination],1)
second = MID(Data[Combination],3,1)
third = RIGHT(Data[Combination])

Then base on the base table ,create another table:

Table = 
UNION (
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[first],
        "Combination", Data[Combination],
        "value", Data[Value]
    ),
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[second],
        "Combination", Data[Combination],
        "value", Data[Value]
    ),
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[third],
        "Combination", Data[Combination],
        "value", Data[Value]
    )
)

Final create visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[v-luwang-msft · Accepted solution]

Hi @JFG123 ，

The way Samarth_18 shared ,only  suitable for cases where the slicer selects only one value.When your slicer selects more than one value, an error will be reported.You could try the following ways.

Output:

Solutions:

Use the following dax to create new column:

first = LEFT(Data[Combination],1)
second = MID(Data[Combination],3,1)
third = RIGHT(Data[Combination])

Then base on the base table ,create another table:

Table = 
UNION (
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[first],
        "Combination", Data[Combination],
        "value", Data[Value]
    ),
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[second],
        "Combination", Data[Combination],
        "value", Data[Value]
    ),
    SELECTCOLUMNS (
        Data,
        "Drop Down", Data[third],
        "Combination", Data[Combination],
        "value", Data[Value]
    )
)

Final create visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[JFG123]

This soln almost answers the question (so close...), this soln does A OR B filtering, I want A AND B when filtering as stated in OP. Is there anyway to modify yours or @Samarth_18 post to get the AND functionality with multiple selections?

[v-luwang-msft · Accepted solution]

Hi @JFG123 ，

It need to adjust on my before pbix file.

Output refer:

Measure:

Measure1 = CALCULATE(DISTINCTCOUNT('Table'[Drop Down]), ALLSELECTED('Table'))
Measure2 = CALCULATE(COUNTROWS('Table'), ALLSELECTED('Table'),'Table'[Combination]=MAX('Table'[Combination]))
Measure3 = IF([Measure2]>=[Measure1],1,BLANK())

You could download my pbix file  and refer it.

Best Regards

Lucien

[JFG123]

Awesome!

[Samarth_18]

Hi @JFG123 ,

You can create a measure like below and use it as filter on your table visual.

_filter =
VAR selected_value =
    SELECTEDVALUE ( Drop_down[Filter_value] )
RETURN
    IF ( CONTAINSSTRING ( MAX ( Data[Comb Value] ), selected_value ), 1, 0 )

Output:-

Thanks,

Samarth

[JFG123]

Can you please provide the sample pbix for your solution 🙂

[Samarth_18]

@JFG123 Please refer below URL:-

https://we.tl/t-4aRstxkzbu