cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

2 independent slicers, one dataset

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.

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

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])

1.PNG2.PNG

 

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))))

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

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])

1.PNG2.PNG

 

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))))

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

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.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.