Forum Discussion

LUCASM's avatar
LUCASM
Helper IV
1 year ago

Conditional formatting

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 

Need Help

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      LUCASM
      Helper IV
      1 year ago

      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.

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    1 year ago

    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

  • LUCASM's avatar
    LUCASM
    Helper IV
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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