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. 

 

My dataset has three main column CLIENT, DATE (Text) and ORDER(Numeric). When I drop the column into a MATRIX, this is what I obtain: 

 

 

Using DAY as a filter, I will be comparing a DAY at a time to TODAY. If TODAY value is less than e.g. Monday then Today will be red, equal will be yellow and greater will be green. This is the desired result: 

 

 

Is there any way I can achieve this using DAX maybe? If yes, can someone help me reach the desired outcome? 

 

Thank you in advance. 

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.

6 Replies

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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.

    • corange's avatar
      corange
      Post Patron
      6 years ago

      Hi @ 

       

      I have tried this and I almost get what I want. 

       

      I can't create a column Week as i dont have a date field at all in my data set. Everything is managed in the SQL Server and I just use a view in BI. The date is straight converted into the weekdays name and today using the date field. 

       

      I have therefore go to step two and created the measure and the colour coding is working. The only thing though is that i dont have the option APPLY To. Please see screnshot below. 

       

      I don't know if that is the reason why but then the conditional formatting doesnt apply to only TODAY but highlight the MONDAY too and following the same rules so it is not matching and confusing. 

       

       

      Thank you. 

    • corange's avatar
      corange
      Post Patron
      6 years ago

      Hi everyone, 

       

      Following my first post, I would like to create another aggregation but not sure what to do about it. 

       

      I would like to see the difference and then the percentage difference between Today and the other day of the week. So when I select Monday and today it gives me the difference between Today job and Monday Jobs and so on for the rest of the week.

       

      Can anyone help me on this? 

       

      Thank you.