Conditional formatting

[LUCASM]

I am trying to set a formatting rule where, IF my product value is < the selected product value then Background colour = PINK, if My Product value > the selected product value then Background colour = LIGHT GREEN

I have tried variations of this but on testing I get no result for my item and no formatting happen.

Strangely, even when it does not find my item, testing the calculation it returnes PINK as a value, but this does not show on my matrix as the colour PINK.

Conditional Format = 

VAR Selected = MAX('Table'[Value])
VAR My_Product = CALCULATE ( MAX ( 'Table'[Value] ) , 'Table'[Brand] = "My_Brand" )

  RETURN 
    SWITCH(
        TRUE(),
        My_Product < Selected, "Pink",
        My_Product > Selected, "Light Green",
        ""
    )     

Link to test file 

[LUCASM]

@danextian, @Jai-Rathinavel 
Thank you for your solutions, I think I have resolved this now using this mod in my code.

VAR My_Product = 
CALCULATE ( 
     MAX ( 'Table'[Value] ), 
     ALL ('Table ),
     'Table'[Brand] = "My_Brand" 
)

Which I believe, removes all filters and then reapplies the brand filter.

[Anonymous]

Hi,@LUCASM 
We are delighted that you have found a solution and are willing to share it.

 If you’re inclined, you might consider accepting this solution, as it could also assist other community members facing the same problem, enabling them to find a resolution more swiftly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu

[Jai-Rathinavel]

@LUCASM Create a disconnected calculated table with the below dax and pull the brand column in a slicer with single selection and paste the format measure below

1. Create a calculated Table ('Brand Slicer') with below dax and pull the column in a slicer

Brand Slicer = VALUES(Table[Brand])

2. Copy paste below format measure and apply conditional formatting

Conditional Format = 
var selected = CALCULATE(SUM(Table[Value]),Table[Brand] = SELECTEDVALUE('Brand Slicer'[Brand]))
var my_product = SUM(Table[Value])
RETURN
SWITCH(TRUE(),
            my_product < selected , "Pink",
            my_product > selected , "Light Green")

Did I answer your question ? Please accept this post as a solution.

Thanks,

Jai

[danextian]

Hi @LUCASM 

Looking at your formula, both the selected product value and my_product are coming form the same table so when you select a product value from the slice, my_product also gets filtered to that specific product value.

Things you can do:

• Change the slicer to a slider and select before - this will include every value up to the max slicer selection
• Use a disconnected table by getting the distinct rows of 'Table'[Value].  Use this column in the slicer and change the change the Selected variable formula to MAX('Table'[Value])​

ValueTable = DISTINCT ( 'Table'[Value]  )

Note:  a disconnected table doens't have a relationship to your fact or dimension tables.

[LUCASM]

Caclulated Tables are not suppoprted in Power BI service.
I had this and I needed to get rid of my Calculated table which is now causing my headache.
Is there no way of ammending the 

VAR My_Product = CALCULATE ( MAX ( 'Table'[Value] ) , 'Table'[Brand] = "My_Brand" )

so it includes all brands and that my Filter then resets it to just my Brand?
I tried using ALL but it doesnt appear to like that either.