Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I'm hoping someone can point me in the right direction here, as my PBI isn't the best.
Currently, I have a report that has a slicer in it, with options of:
Secure API
Secure Code
Secure Infrastructure
etc...
When a user choose one of those slicer options, it changes the data displayed on the table within the report, usual PBI stuff 😊 but what I'm trying to figure out is to filter the data in the table based on the slicer option choosen.
For example:
If they choose Secure API, I would like the table to only show results that have 'Devops' & 'CD' in it.
If they choose Secure Code, I would like the table to only show results that have 'Devops', 'CD' & 'Classic' in it.
If they choose Secure Infrastructure, I would like the table to only show results that have 'Classic' in it.
etc..
I think it is possible, but seems my Google-Fo skills isn't quite pointing me in the right direction to help me get to what I'm trying to achieve.
Any helps or pointers are greatly appreciated.
Many thanks 👍
Solved! Go to Solution.
Try creating a measure to use as a filter for the visual. You will need an independent table for the slicer selection
Let the independent table values be DSlicer[Value], the fact table be FactTable and the selection column be FactTable[Selection])
Filter measure =
SWITCH (
SELECTEDVALUE ( DSlicer[Value] ),
"Secure API",
COUNTROWS (
CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Devops", "CD" } )
),
"Secure Code",
COUNTROWS (
CALCULATETABLE (
FactTable,
FactTable[Selection] IN { "Devops", "CD", "Classic" }
)
),
"Secure Infrastructure",
COUNTROWS (
CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Classic" } )
)
)
Select the visual and add this measure as a filter, setting the value to greater or equal to 1
Proud to be a Super User!
Paul on Linkedin.
Just wanted to say thank you for the above and this seems to have resolved my issue 😀
No idea how you guys do it and have all this knowledge, but the above solution with the measure helped achieve what I wanted, so a big thank you from me!!
Hi @Begbie ,
Define a dimension table (dimSlicer) like this:
| slicerValue | codeToInclude |
-----------------------------------
| Secure API | Devops |
| Secure API | CD |
| Secure Code | Devops |
| Secure Code | CD |
| Secure Code | Classic |
| ...
Define a bridge table (codeBridge) that is just a unique list of codes to include:
| codeToInclude |
-------------------
| Devops |
| CD |
| Classic |
| ...
In your data model, relate as follows:
- dimSlicer[codeToInclude] MANY : ONE codeBridge[codeToInclude] (filter direction BOTH)
- codeBridge[codeToInclude] ONE : MANY factTable[Code]
Use dimSlicer[slicerValue] in your page slicer.
Pete
Proud to be a Datanaut!
Hi Pete,
Thank you for the pointers and that has seemed to help with the solution, but seems I now have double / triple entries in my table 😣
This is what is being shown when I use the dimSlicer suggestion you gave above:
Whereas before it would show:
I'm sure it's probably down to something silly.
Thanks.
Hi @Begbie ,
Where did you get this column from?
It appears to be what is causing the row duplication.
I don't think this column should come from either of the tables that we added before, but rather from the [code] column in your fact table.
Pete
Proud to be a Datanaut!
Hi @BA_Pete
Thank you for coming back to me. Yes, it did seem I did choose the wrong column initially, but then I had other things happen where the data didn't filter quite correctly, but PaulDBrown solution seems to have helped me for what I needed to do.
Thanks again 😊
Try creating a measure to use as a filter for the visual. You will need an independent table for the slicer selection
Let the independent table values be DSlicer[Value], the fact table be FactTable and the selection column be FactTable[Selection])
Filter measure =
SWITCH (
SELECTEDVALUE ( DSlicer[Value] ),
"Secure API",
COUNTROWS (
CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Devops", "CD" } )
),
"Secure Code",
COUNTROWS (
CALCULATETABLE (
FactTable,
FactTable[Selection] IN { "Devops", "CD", "Classic" }
)
),
"Secure Infrastructure",
COUNTROWS (
CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Classic" } )
)
)
Select the visual and add this measure as a filter, setting the value to greater or equal to 1
Proud to be a Super User!
Paul on Linkedin.
Just wanted to say thank you for the above and this seems to have resolved my issue 😀
No idea how you guys do it and have all this knowledge, but the above solution with the measure helped achieve what I wanted, so a big thank you from me!!
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |