Forum Discussion

rjsidek's avatar
rjsidek
Icon for Helper II rankHelper II
6 years ago
Solved

Getting Slicer to show only one value

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 option...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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/EeiO4lKdddtBsAJowgbGlLwB9Xhp5rA1ZzIzvv6qouS9mw?e=gARlmU

     

     

    Hope this would help.

     

    Best Regards,

    Kelly

     

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

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/EeiO4lKdddtBsAJowgbGlLwB9Xhp5rA1ZzIzvv6qouS9mw?e=gARlmU

 

 

Hope this would help.

 

Best Regards,

Kelly

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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