Getting Slicer to show only one value

[rjsidek]

Hi everyone,

I have a sample excel dataset that looks like this:

The excel is getting filled via a Microsoft Forms that people fill. For Sponsor name, they can choose a maximum of 2 options, hence why certain row values have more than 1 person.

The issue is this:

In my powerBI report, I want to add a slicer that allowns them to filter by sponsor name. By right, the slicer should have only 6 options, Person 1 through 6. 

This is how my current slicer looks like:

As you can see, there is an option that includes more than one person. I want to be able to amend this such that the slicer only shows option for each individual person 1 through 6.

Any help or suggestions is appreciated!

[v-kelly-msft · Accepted solution]

Hi @rjsidek ,

You need to create a new table, which contains all the categories you want:

Then you need to create a measure as below:

Measure = var p1 = MAX('Table (2)'[slicer])
var p2 = MIN('Table (2)'[slicer])
var if1 = SEARCH(p1,MAX('Table'[Name]),1,BLANK())
var if2 = SEARCH(p2,MAX('Table'[Name]),1,BLANK())
return
IF(if1 <> BLANK() || if2 <> BLANK(),1,BLANK())

Finally put the measure and column together into a table visual, and put the value of the new table into a slicer, you will see as below:

For the related .pbix file,pls turn to the URL below:https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EeiO4lKdddtBsAJowgbGlLw...

Hope this would help.

Best Regards,

Kelly

[Anonymous]

Measure = var p1 = MAX('Table (2)'[slicer])
var p2 = MIN('Table (2)'[slicer])
var if1 = SEARCH(p1,MAX('Table'[Name]),1,BLANK())
var if2 = SEARCH(p2,MAX('Table'[Name]),1,BLANK())
return
IF(if1 <> BLANK() || if2 <> BLANK(),1,BLANK())

Just wondering how I adjust the attached formula if they are able to choose more than 2 people's names? Thanks for your advice

[sturlaws]

Hi @rjsidek,

to solve this there are two possible strategies, and both involve Power Query.

Alternative 1) For the rows where you have more than 1 Sponsor Name you can create new rows so that Sponsor Name only has 1 value for each row. This means you will have almost duplicate rows, except for the Sponsor Name column. The drawback of this solution is that it will make any DAX code more complex. On the positive side, you don't have to resolve to Many-to-many relationship with this alternative.

Alternative 2) Create a dimension for the sponsors, and use Many-to-many relationship between the dimension and your table. The drawback is of course that you have the Many-to-Many relationship, while your DAX code may be kept relative simple.

I have created a simple mockup of alternative 2. The main work is done in Power Query/Edit Queries: .pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.