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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lm_i
Helper II
Helper II

Conditional Formatting on a text field based on Slicer selections from a separate dataset

Hello,

 

I am trying to "highlight" the background of each item that falls under a specific category of a slicer.

The slicer however comes from a separate dataset i.e. the items come from one data table and the slicer/filter comes from another data table.

I created a relationship between both data tables in order to use the specific slicer.

 

Attached is a picture to give some context (sensitive info blocked out).

Is it possible to format the background (add colour) to specific lines under Description based on the slicer highlighted to the right?

InkedInkedInkedPBI_LI.jpg

Any guidance on this will be appreciated.

9 REPLIES 9
nandic
Memorable Member
Memorable Member

Hi @lm_i ,
There are 3 steps:
1) created disconnected table - it will prevent slicer from filtering main list so you will be able to only highlight selected values
2) create measure like this: 

SelectedManager = IF(SELECTEDVALUE('Manager (2)'[Manager Name]) = MAX(Manager[Manager Name]),1,0)
*I am using manager slicer in my demo, table Manager is original, table Manager 2 is disconnected table
3) assign background color conditional format based on this measure
Full description and steps: https://stackoverflow.com/questions/59930844/highlighting-with-slicer-power-bi

parry2k
Super User
Super User

@lm_i sure, you can add a measure for the color and then use this measure in conditional formatting of the description, using field value

 

Color = 
IF ( SELECTEDVALUE ( TableSlicer[Column] ) = MAX ( Table[Column] ), "Red" )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you both @parry2k  and @nandic for your responses.

 

I am a novice at power bi so my explanations may not be the most concise.

 

The data in the visual is linked to the data in the slicer by another table, i.e. there are 3 data tables, the one in the middle linking the *visual data* to *slicer data*. 

The fields highlighted in the picture are text fields.

 

Also, the data in the slicer is different from the data to be highlighted i.e. the Description column is different from what the Slicer (Core HT).

 

Please let me know if any other information would be useful.

 

Hi, @lm_i 

Do you mean that these two tables are not directly related to each other?

Is it convenient to show the view of the relationship between your table and the table (using the option insert photos)?

Ex:

24.png

 

Best Regards,
Community Support Team _ Eason

Hi @v-easonf-msft ,

 

Yes, both tables are not directly related to each other.

 

All the relationships are not able to fit into one picture but I will post them, hopefully you can follow.

 

112233

Hi , @lm_i 

Try measure as below :

Measure1 =
VAR _cotc =
    CALCULATETABLE (
        VALUES ( 'Brands&COTC'[COTC] ),
        FILTER (
            ALL ( 'Brands&COTC' ),
            'Brands&COTC'[COTC] IN FILTERS ( Slicer[slicer_COTC] )
        )
    )
VAR _sku =
    CALCULATETABLE (
        VALUES ( 'Brands&COTC'[SKU Desc] ),
        FILTER ( ALL ( 'Brands&COTC' ), 'Brands&COTC'[COTC] IN _cotc )
    )
VAR _skureflect =
    CALCULATETABLE (
        VALUES ( 'Brand&SKU'[Description] ),
        FILTER ( ALL ( 'Brand&SKU' ), 'Brand&SKU'[SKU Desc] IN _sku )
    )
VAR _description =
    CALCULATETABLE (
        VALUES ( 'Data Table'[Customer ID] ),
        FILTER ( ALL ( 'Data Table' ), 'Data Table'[Description] IN _skureflect )
    )
RETURN
    IF ( MAX ( 'Data Table'[Customer ID] ) IN _description, 1, 0 )

Then apply it to background conditional formatting.

28.png

 

 

29.png

 

 

pbix attached

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-easonf-msft ,

 

Thank you for your solution.

 

It first highlights all Item Descriptions when no filter selections are made. When COTC Slicer is applied, only those Item Descriptions which fall under that category are highlighted.


Also, I am not able to mark your answer as the solution because "Authentication Failed" error occurs.

Hi, @lm_i 

It first highlights all Item Descriptions when no filter selections are made. When COTC Slicer is applied, only those Item Descriptions which fall under that category are highlighted.

 

Sorry, I am not able to  understand what you mean.
Has your problem been resolved? If there is something that does not meet your expected results, can you explain it in more detail.

 

Best Regards,
Community Support Team _ Eason

 

Hi @v-easonf-msft ,

 

Yes your solution works.

 

In my previous reply I was trying to explain what was the result of the measure you shared.

 

Sorry for the confusion.

 

Thank you for your assistance!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.