Forum Discussion

corange's avatar
corange
Post Patron
6 years ago
Solved

Conditional Formatting - Less / Greater than

Hi POWER BI Team,    I need some help with a project. I have looked around but could not find an answer and the conditonal formatting function in the format section doesn't do the trick for me.   ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi corange 

    I have a solution below though it is a little different from your final expected result.

    week is a column

    week = IF(TODAY()=[date],"today",FORMAT([date],"dddd"))

    [Measure] is a measure

    Measure =
VAR sl =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[client] = MAX ( 'Table'[client] )
                && 'Table'[week] <> "today"
        )
    )
VAR t =
    SUM ( 'Table'[value] )
RETURN
    IF (
        MAX ( [week] ) = "today",
        SWITCH (
            TRUE (),
            t > sl
                || (
                    t <> BLANK ()
                        && sl = BLANK ()
                ), 1,
            t = sl
                || (
                    t = BLANK ()
                        && sl = BLANK ()
                ), 0,
            t < sl
                || (
                    t = BLANK ()
                        && sl <> BLANK ()
                ), -1
        )
    )

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

amitchandak's avatar
amitchandak
Super User
6 years ago

corange , Create color measure like this 

color =
switch ( true(),
FIRSTNONBLANK(Table[Date],"NA") ="Today" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[Date],"NA") ="Today" && sum(Table[Value]) >1000,"red",
/// Add more conditions
"blue"
)

 

then use that using field in conditional formatting

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

  • corange's avatar
    corange
    Post Patron
    6 years ago

    Hi,

     

    Thanks for your answer. I have tried following your instructions but I am totally lost here. 

     

    The formula you mentionned doesnt figure in the article and I dont know where to start of even apply the steps they are saying as the scenario is different from mine. 

     

    Anyone else has some insight on this? 

     

    Thank you. 

    • corange's avatar
      corange
      Post Patron
      6 years ago

      Hi, 

       

      Where i am lost as well is the value used in the formula > 500 , > 1000 and so on. 

       

      I dont understand how they are relevant.