2 independent slicers, one dataset

[Anonymous]

I have a dataset of projects, with columns including Project_ID, Project_Name, StartDate, EndDate, etc .....

Each record has a unique Project_ID and Project_Name

I have a report based on that dataset, with a slicer to select a single project by Project_ID. However, some users will wish to select a project by Project_Name, others will wish to select by Project_ID.

Is there a neat, simple (or not so simple) way to have a slicer or slicers that allows the choice of selecting a project either by Project_ID, OR by Project_Name? In other words, if a project has already been selected (by ID), how could someone then select the next one by Name?

The only way I've come up with so far is to have a Reset button that clears all filters, which is not very elegant.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Create two extra tables which are unrelated to source data table, to list all available slicer items.

ProjectID = VALUES(Project_Table[ProjectID])
ProjectName = values(Project_Table[ProjectName])

Add above fields into two slicers. Display data from source dataset into your wanted visual. Create below measure, add it into visual level filter and set its value to1.

Measure = var isfilterID=ISFILTERED(ProjectID[ProjectID])
var isfilterName=ISFILTERED(ProjectName[ProjectName])
return
IF(isfilterID=TRUE()&&isfilterName=TRUE(),
IF((SELECTEDVALUE(Project_Table[ProjectID]) in VALUES(ProjectID[ProjectID])) || (SELECTEDVALUE(Project_Table[ProjectName]) in VALUES(ProjectName[ProjectName])),1,0),
IF(isfilterID=TRUE()&&isfilterName=FALSE(),
IF(SELECTEDVALUE(Project_Table[ProjectID]) in VALUES(ProjectID[ProjectID]),1,0),
IF(isfilterID=FALSE()&&isfilterName=TRUE(),
IF(SELECTEDVALUE(Project_Table[ProjectName]) in VALUES(ProjectName[ProjectName]),1,0),
IF(isfilterID=FALSE()&&isfilterName=FALSE(),1,0))))

Best regards,

Yuliana Gu

[Anonymous]

Thank you very much @v-yulgu-msft , that is very neat 🙂

I've tried it out with one visual and it works nicely. I shall now have a think to see if I can make it so that when a value from one slicer is selected, the other slicer value is deselected. 

Thanks again for the great reply.