Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |