Conditional Formatting - Less / Greater than

[corange]

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. 

[v-juanli-msft · Accepted solution]

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]

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. 

[corange]

Hi @ v-juanli-msft

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. 

[amitchandak]

@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-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[corange]

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]

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.