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.
It is not uncommon that I set filters while creating my PowerBI report, forget what I selected and wonder about the figures.
As I was used to work with other BI-tools, having a "current selection" box was something that I got used to and never expected to miss it so hard in PowerBI.
Reading quite some entries in the forum - that were some years old already - I first created a single-line indicator showing the selected values and then switched to a multi-line variant - but still this did not feel "nice".
The single-line for all selections was impractical to read, but had a handling for "no selections" - as PowerBI does not handle this directly.
The multi-line was better - but did not handle the possibility of multiple selections in a field.
But still, both were impractical if you want to show selections from multiple fields with the option to have multiple values selected.
So here is the solution I am now working with, which is a combination of the single-line and multi-line approach - combined with a switch and a separate table.
It is still not a perfect solution, as you need to define the fields that you want to show (i.e.: it is not automatically showing what is selected unless you do not tell PowerBI which fields should be shown here).
Also it is just a display visual and not a space where you can also adapt filters (like e.g. in Qlik tools).
However, it is still sufficient in showing the filters in your slicers
In Case anyone has further improvements to this or even is crazy enough to write a custom visual - feedback welcome! 🙂
So here is how:
1. Create a new table
- go to power query editor
- choose "Enter data"
- create a new table (e.g. "SelectableColumns") with one Column (e.g. "ColumnName")
- as values, add all the Slicers you want to show in your Current Selection box
2. Create a measure in your SelectableColumns table: (this example has 3 slicers - but can easily be extended)
Measure =
Switch(SELECTEDVALUE(SelectableColumns[ColumnName]),
"Slicer 1",
IF (
COUNTROWS(ALLSELECTED(Table1[Slicer1])) <> COUNTROWS(ALL(Table1[Slicer1])),
CONCATENATEX(ALLSELECTED(Table1[Slicer1]),Table1[Slicer1],","),
"Unselected"
),
"Slicer 2",
IF (
COUNTROWS(ALLSELECTED(Table2[Slicer2])) <> COUNTROWS(ALL(Table2[Slicer2])),
CONCATENATEX(ALLSELECTED(Table2[Slicer2]),Table2[Slicer2],","),
"Unselected"
),
"Slicer 3",
IF (
COUNTROWS(ALLSELECTED(Table1[Slicer3])) <> COUNTROWS(ALL(Table1[Slicer3])),
CONCATENATEX(ALLSELECTED(Table1[Slicer3]),Table1[Slicer3],","),
"Unselected"
)
)
3. Create a table visual, where you add the column SelectableColumns[ColumnName] and your new Measure
I just wanted to post this for any future requests, when someone is switching from another BI-tool to PowerBI and desparately looking for a way to show all the filters in an application that currently apply.
Hi @CoTheiss,
Thanks for your sharing, these contents will be helpful if other community users who faced the similar requirements.
Regards,
Xiaoxin Sheng
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |