Forum Discussion

cn4422's avatar
cn4422
Helper V
9 months ago
Solved

Slicer for Multiple-Choice Values

Hi,

I have a fact table containing values from a website form where multiple choices are possible.

My goal was to create a dimension table and use it as a slicer. However, it only works with exact matches and not with contains. This means that if I select "1" in the slicer, only the exact "1" values from the fact-table are filtered, while entries like "1,2" or "1,3" are excluded.

If anyone has an idea on how to solve this issue, it would be much appreciated! 🙂

 

 

Need Help
  • v-hashadapu's avatar
    v-hashadapu
    9 months ago

    Hi cn4422 , Thank you for reaching out to the Microsoft Community Forum.

     

    You don’t need to split the main fact table directly. Instead, create a reference table from it in Power Query, keep only the record ID and the multi-choice column. Then split that column by commas into rows, so each choice becomes its own row. This new table acts as a bridge between your fact table and the dimension table (link ID -> fact and choice value -> dimension). Now your slicer from the dimension table will correctly filter all rows that contain the selected value, even when it’s part of a comma-separated list.

14 Replies

    • cn4422's avatar
      cn4422
      Helper V
      9 months ago

      HI ryan_mayu ,

       

      thanks for your reply!

       

      In the fact table, there are other columns as well - isn't this problematic if they all get additonal rows?

       

      I probably should have mentioned, that in the fact table there are other columns as well.

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        9 months ago

        cn4422  I am not sure if other columns will affect this solution or not. Depends on what's the expected output that you want. Pls update the sample data and expected output.

  • lbendlin's avatar
    lbendlin
    Super User
    9 months ago

    Change your commas to pipe. Then use PATHCONTAINS in your measure that you use as the visual filter.

    • cn4422's avatar
      cn4422
      Helper V
      9 months ago

      Hi lbendlin,

       

      thanks for your reply!

       

      I tried the approach with "PATHCONTAINS" but I didn't succed.

       

      I tried two measures

       

      Product_Selector =
      VAR SelectedID = SELECTEDVALUE('dimProduktauswahl'[Produktauswahl])
      RETURN
      IF(
      PATHCONTAINS('lead'[Produktauswahl_Pipe], SelectedID & ""),
      1,
      0
      )

       

      and

       

      Product_Selector =
      VAR SelectedID =
      SELECTEDVALUE('dimProduktauswahl'[Produktauswahl])
      RETURN
      IF(
      PATHCONTAINS(
      PATH('lead'[pre_product_selection_pipe]),
      SelectedID & ""
      ),
      1,
      0
      )

       

      but couldn't get it to work since with "PATH" I could only select measures and not my other tables.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    9 months ago

    Hi,

    Create the dim table by removing duplicates from the multiple choice answer column.

  • Rufyda's avatar
    Rufyda
    Super User
    9 months ago

    Split the multi-choice column into rows in Power Query:

    Duplicate the column.

    Split by delimiter (comma) → Split into rows.

    Use this new column in your slicer.
    Use PATHCONTAINS() with a measure if you cannot split the column.

    If this helps, please mark as Accepted Solution and drop a "Kudos"

    • cn4422's avatar
      cn4422
      Helper V
      9 months ago

      Rufyda Thanks for your reply!
      Do I need to make a new reference table first? (or directly start  with "duplicate the column in the fact table?)

       

      I probably should have mentioned, that in the fact table there are other columns as well.

      • v-hashadapu's avatar
        v-hashadapu
        Community Support
        9 months ago

        Hi cn4422 , Thank you for reaching out to the Microsoft Community Forum.

         

        You don’t need to split the main fact table directly. Instead, create a reference table from it in Power Query, keep only the record ID and the multi-choice column. Then split that column by commas into rows, so each choice becomes its own row. This new table acts as a bridge between your fact table and the dimension table (link ID -> fact and choice value -> dimension). Now your slicer from the dimension table will correctly filter all rows that contain the selected value, even when it’s part of a comma-separated list.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    9 months ago

    Hi cn4422 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

    • cn4422's avatar
      cn4422
      Helper V
      9 months ago

      Hi v-hashadapu ,

      thanks for your message.

      I'll hopefully be testing the suggested approaches tomorrow and will then gladly post an update on how it went. ðŸ˜Š

      • v-hashadapu's avatar
        v-hashadapu
        Community Support
        9 months ago

        Hi cn4422 , Thanks for the update. Please do share the results once you have had the chance to test it.