Forum Discussion

JFG123's avatar
JFG123
Frequent Visitor
4 years ago
Solved

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

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

 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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's avatar
    v-luwang-msft
    4 years ago

    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

     

     

     

7 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      JFG123
      Frequent Visitor
      4 years ago

      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's avatar
        v-luwang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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