Forum Discussion
Helper IIFilter on Icons ?
Is there a quick and easy way to filter on icons from a measure?
So my measure is just
And ranges below:
To select on Icon (Red, Yellow or Green) what would be the most easiest way to do it? I was thinking nested if and button selector but I wasn't sure if it would work with a measure.
Hi kylee_anne
Create a measure to categorise the result (replace my generic 10s with your numbers)
H1 Icon Category = SWITCH( TRUE(), [H1] < -10, "Red", [H1] >= -10 && [H1] <= 10, "Yellow", [H1] > 10, "Green" )Create a new table to use as a slicer
Icon Filter = DATATABLE("Icon", STRING, {{"Red"}, {"Yellow"}, {"Green"}})Add the 'Icon' column from the new table to a slicer
Create a measure to use as a visual filter
Show Row = IF( SELECTEDVALUE('Icon Filter'[Icon]) = [H1 Icon Category] || ISBLANK(SELECTEDVALUE('Icon Filter'[Icon])), 1, 0 )Ensure the visual filter on your table is 'show row = 1'
Please give a thumbs up and mark as solved if this helps, thanks!
10 Replies
- wardy912
Super User
Hi kylee_anne
Create a measure to categorise the result (replace my generic 10s with your numbers)
H1 Icon Category = SWITCH( TRUE(), [H1] < -10, "Red", [H1] >= -10 && [H1] <= 10, "Yellow", [H1] > 10, "Green" )Create a new table to use as a slicer
Icon Filter = DATATABLE("Icon", STRING, {{"Red"}, {"Yellow"}, {"Green"}})Add the 'Icon' column from the new table to a slicer
Create a measure to use as a visual filter
Show Row = IF( SELECTEDVALUE('Icon Filter'[Icon]) = [H1 Icon Category] || ISBLANK(SELECTEDVALUE('Icon Filter'[Icon])), 1, 0 )Ensure the visual filter on your table is 'show row = 1'
Please give a thumbs up and mark as solved if this helps, thanks!
kylee_anneSorry it took me ages to work out how to do this. Currently if I want to "Select All" on my filter it show's nothing. How do I modify to "Select All"?
- burakkaragoz
Hi kylee_anne ,
Great question — filtering directly on icons (like Red, Yellow, Green) that are applied via conditional formatting isn't natively supported in Power BI visuals. However, there’s a solid workaround using a helper column or measure that mimics the icon logic and allows you to filter accordingly.
Here's how you can do it:
- Create a new measure or column that returns a label or numeric code based on the same logic you used for the icon formatting. For example:
H1 Status = SWITCH( TRUE(), [H1] < -0.1, "Red", [H1] < 0.1, "Yellow", "Green" )Use this new field in a slicer\ Add a slicer to your report using the H1 Status field. This lets users filter the table by icon category (Red, Yellow, Green), even though the actual icon is just visual formatting.
Keep the icons\ You can still use your original icon formatting on the [H1] measure in the table — this new field is just for filtering.
This approach gives you full control over filtering while keeping the visual appeal of icons.
For more on conditional formatting in Power BI, check out the official docs:
Microsoft LearnLet me know if you want help adapting the logic to your exact thresholds!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.- danextian
Here comes another AI copy-pate response from you that isn't validated.
Create a new measure or colum/Use this new field in a slicer. Measures cannot be used in a slicer as it doesnt have a row context and calculated columns isnt dynamic - its values won't change in response to slicer selections/cross-filtering. And since H1 involves two tables, which table then to add this calc column to?
- burakkaragoz
Thank you for your contributions danextian , and I appreciate you improving @wardy212's solution. I'm focused on helping community members solve real problems and doing AI tagging doesn't contribute to that goal.
Let me add some technical insights to enhance the discussion:
1. Performance Optimization Points:
VAR _FilteredData = FILTER( 'table', [@color] IN VALUES('Icon Filter'[Icon]) )
This approach would be more efficient because:- It reduces the memory footprint
Applies filtering before expensive calculations
Improves query response time
For large datasets, we could further optimize by:
Pre-calculating certain values
Using variables to prevent multiple recalculations
Implementing proper filter context
I'm always happy to discuss implementation details or run through specific scenarios if anyone needs help. Let's keep focusing on solving technical challenges together.
- It reduces the memory footprint
- kylee_anne
Funnily enough I keep getting this field can't be used here because a non measure field is required when I try using this solution. ? What am I doing wrong?
- danextian
Because his reponse is AI copy-paste which he didn't even validate. Funny enough his reply to me is another AI copy-paste.
- danextian
Hi kylee_anne
If you're filtering visuals with row context, like tables or matrices, you can use the approach suggested by wardy912 . But for visuals without dimension fields, such as KPIs or cards, you’ll need to evaluate the measure over a table or table expression. Here's an example formula:
Measure by Color = SUMX ( FILTER ( -- Summarize by relevant columns and calculate color per row SUMMARIZECOLUMNS ( 'table'[column1], 'table'[column2], "@color", SWITCH ( TRUE (), [Schedule Health] >= -1 && [Schedule Health] < -0.03, "Red", [Schedule Health] >= -0.03 && [Schedule Health] < 0, "Yellow", [Schedule Health] >= 0 && [Schedule Health] <= 1, "Green" ), "@h1", [H1] ), -- Keep only selected colors from the slicer [@color] IN VALUES ( 'Icon Filter'[Icon] ) ), -- Sum the H1 values for filtered rows [@h1] )'Icon Filter' is the DAX calc table as per wardy912's solution.
Hi kylee_anne ,
Thank you community members for the helpful repsonses!
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.If still have the issue, feel free to reachout!
Thank you.
