Slicer for Multiple-Choice Values

[cn4422]

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! 🙂

 

[v-hashadapu]

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]

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]

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

[Rufyda]

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]

@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 · Accepted solution]

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.

[ryan_mayu]

@cn4422 

here is a workaround for you,

create a duplicated column for fact table and split by comma to rows.

then create a measure and add that measure to visual filter and set to 1.

Pls see the attahment below

[cn4422]

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]

@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]

@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]

you are welcome

[Ashish_Mathur]

Hi,

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

[lbendlin]

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

[cn4422]

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.