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.

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.