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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.