Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rjsidek
Helper II
Helper II

Getting Slicer to show only one value

Hi everyone,

 

I have a sample excel dataset that looks like this:

Persons.png

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:

Slicer.png

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!

 

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @rjsidek ,

 

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

131.png

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:

 

141.png

 

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

 

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi @rjsidek ,

 

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

131.png

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:

 

141.png

 

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
Not applicable

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
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors