Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering a value in a slicer and coloring it by different color in another chart

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. 

KatyaK_0-1633261446791.png

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. 

1 ACCEPTED SOLUTION

Sure! File attached...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

12 REPLIES 12
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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.

Model.JPG

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.)

Simple highlight.JPG

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:

Synch1.JPG

 

Synch2.JPGNow the slycers are synched.

Next select the Normal slicer and turn off the interactions with the visual you wish to highlight values.

Interactions.JPG

Finally select the highlight slicer and under View, Selection, hide the slicer

HideSlicer.gif

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.

Highight.gif

 

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

PaulDBrown
Community Champion
Community Champion

Can you show a depiction of what you would like to achieve please?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

Capture1.PNGCapture2.PNG

PaulDBrown
Community Champion
Community Champion

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:

gradient.png

 to get:

gradient.gif

 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

downside.png

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")

 

hide.gif

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:

diff colours.png

colours11.gif

 

 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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.