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.
Hello!
I have a slicer and a cluster bar chart in my report.
What I want is to filter a value in the slicer so this value will be colored in different color in the cluster bar chart as well.
Let's say I filtered an employee number 0004353063 (as showcased in the image) -> when I click on the ID I want that all the other IDs in the chart will be shown but only the selected ID will be colored by different color.
Anyone knows how to do it?
Thanks in advance.
Solved! Go to Solution.
Sure! File attached...
Proud to be a Super User!
Paul on Linkedin.
If you use the same column used in your visual, it will filter the data. You may need to add a separate disconnected table to use in the your slicer with a table expression like this.
EmployeeSlicer = DISTINCT(Table[EmployeeID])
Once you have that (with no relationship between the tables), you can write a measure to dynamically return the color for your bar to be used in the Data Color format in your chart.
BarColor = var slicervalue = SELECTEDVALUE(EmployeeSlicer[EmployeeID])
var thisvalue = MIN(OriginalTable[EmployeeID])
return IF(thisvalue = slicervalue, "Red", "Blue")
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat!
Thank you for your response.
In the example I showcased in the image I used employee id (numerical type) but what If I want to use textual data, for example employee name? (because here I won't be able to use the MIN function).
To elaborate on @mahoneypat 's suggestion.. If you want to highlight for multiselected values, you will need a different measure.
For example, take this model where the table Highlight Channel Values is a copy of the Dim Channel table.
With this measure for conditional formatting,
Highlight Values =
VAR Selec =
COUNTROWS (
INTERSECT (
VALUES ( 'Hilight Channel values'[HChannel] ),
VALUES ( 'DIM Channel'[Channel] )
)
)
RETURN
IF ( ISFILTERED ( 'Hilight Channel values'[HChannel] ), Selec, 2 )
You will get this (the chart is set up with the regular Channel Dimension table.)
You can also add other visuals to the report page with the normal filters. This is how:
Add the visuals as you would normally do, with the slicer's from the dimension tables with relationships in the model, including the slicer for the values you wish to highlight & the corresponding normal slicerr for the values. To synch the normal slicer with the highlight slicer, under View in the ribbon, select Synch Slicers. Select one of the relevant slicers, and under Advanced options, type in a name in group name box. Select the corresponding slicer and do the same:
Now the slycers are synched.
Next select the Normal slicer and turn off the interactions with the visual you wish to highlight values.
Finally select the highlight slicer and under View, Selection, hide the slicer
Now the regular visuals will be filtered as expected, and the highlight visual will highlight selected Channel values, but also respond to the other silcers.
Proud to be a Super User!
Paul on Linkedin.
Such a cool method @PaulDBrown . Congratulations for working this out and thanks for sharing. You may have earned yourself a solutions stalker (I am going to review all your solutions ... there will be gold in them there hills for sure).
Hi @PaulDBrown ,
Good solution,could you pls also share your .pbix file?
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Sure! File attached...
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown , Thanks a lot for this solution, Could you please help me to get multiple colors for each bar, it may be based on specific value or ranking
Can you show a depiction of what you would like to achieve please?
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrownthanks a lot for your response. Please find the attached screenshot which I would like to achieve, basically the selected data should show in multiple color instead of single color. Below are the examples
Ok, here are a couple of ways of doing this.
1) Gradient colour based on a value (in my example, the sales value displayed on the chart)
Use a measure along the lines of:
Selected Values gradient =
VAR Selec =
COUNTROWS (
INTERSECT (
VALUES ( 'Hilight Channel values'[HChannel] ),
VALUES ( 'DIM Channel'[Channel] )
)
)
RETURN
IF (
AND ( ISFILTERED ( 'Hilight Channel values'[HChannel] ), Selec = 1 ),
[Sum of Sales],
"blue"
)
Add the measure to the conidtional formatting option for columns and set the colours:
to get:
The downsside as regards the UX is that you get a legend for the gradient if there is no selection which doesn't make any sense; the upside is that you don't have to worry about how many selections are made
You can actually "hide" the gradient legend using a rectangular shape overlay and with conditional formatting using this measure:
Hide card = IF(ISFILTERED('DIM Channel'[Channel]), "#FF000000", "white")
2) Select the colours you wish to show based on a RANK measure for example:
Rank by sales =
VAR Selec =
COUNTROWS (
INTERSECT (
VALUES ( 'Hilight Channel values'[HChannel] ),
VALUES ( 'DIM Channel'[Channel] )
)
)
RETURN
IF (
COUNTROWS ( ALL ( 'DIM Channel' ) )
= COUNTROWS ( ALLSELECTED ( 'Hilight Channel values'[HChannel] ) ),
0,
IF (
AND ( ISFILTERED ( 'DIM Channel'[Channel] ), Selec = 1 ),
RANKX (
FILTER (
ALLSELECTED ( 'DIM Channel' ),
'DIM Channel'[Channel] IN VALUES ( 'Hilight Channel values'[HChannel] )
),
[Sum of Sales],
,
DESC,
DENSE
)
)
)
You then use this measure in the conditional formatting interface for columns, and set the colours you choose based on the RANK value:
The issue you have in this case is that you need to set a colour to cover the whole range of selections (so if there are 20 possible selections, you would need to set the conditional colour for a rank up to 20). You would also probably need to set up an "external" legend visual (see example gif) to provide the information needed to explain the colour coding.
Hope this helps!
New file attached
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown Thanks a ton for the help, all the above solutions works perfectly for my requirements. Thanks again!
Hi @PaulDBrown ,
Many thanks!
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |