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

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.

  • cn4422's avatar
    cn4422
    Helper V
    9 months ago

    ryan_mayu thanks again for your help.

    In the end, I created a bridge-table so that the orignal table wouldn't be expanded in terms of additional rows.

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      9 months ago

      you are welcome