Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
10 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
    10 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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
10 months ago

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

  • cn4422's avatar
    cn4422
    Icon for Helper V rankHelper V
    10 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.