Colour filter day

[Anonymous]

Hi,

Based on a previous post of me (Click), I want to give the days in the past a colour. How can I do that? Is that possible in Power BI? 

Thanks!

[amitchandak]

@Anonymous , you can create a color measure like this (using switch true() or  if )

Color Date = if(FIRSTNONBLANK(Table[date],TODAY()) <today(),"lightgreen","red")

And in conditional formatting choose "field" option and use this measure.

Refer :
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

[Anonymous]

@amitchandak Im not able to choose this measure. I can choose other measure which I created but this one is not possible to choose.

[amitchandak]

@Anonymous , as long you create the new one as a measure you should able to do that.

[Anonymous]

@amitchandak Is it possible to expand this measure? I have percentages for the dates. Can I also add colours for the percentages, for example if a percentage is higher dan 80% than the colour should be orange.

[amitchandak]

@Anonymous , you can create as complex as you want using if or switch and using measure or paramter values

example

Color Date = if(FIRSTNONBLANK(Table[date],TODAY()) <today(),"lightgreen","red")

if(FIRSTNONBLANK(Table[Value],"true") "true","green","red")


Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

[Anonymous]

@amitchandak But it is not possible to add in this measure another measure? I need to define the colours based on a measure I created.

[v-gizhi-msft · Accepted solution]

Hi,

After my test, i think you should not add other check measure into original measure because the merged measure will apply all conditions that you defined.

See this, as below shows, the [Date] and [Percentage] both apply two conditions in switch function.

Measure =
SWITCH (
    TRUE (),
    MAX ( 'Table'[Date] ) < TODAY (), "#FF0000",
    MAX ( 'Table'[Percentage] ) > 0.8, "#dd7c19"
)

So i advise you create two color measures in this case and apply them to two fields.

Like this:

Color_1 = IF(MAX('Table'[Date])<TODAY(),"#FF0000")

Color_2 = IF(MAX('Table'[Percentage])>0.8,"#dd7c19")

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft This seems useful. However I don't know how you added the measures to the columns. Also with downloading the file I can't figure it out. Could you explain that to me?

[v-gizhi-msft]

Hi,

See this:

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft I am making this measure now but it seems that it is not possible to use in this measure another measure. I calculate my percentages based on a measure.

[v-gizhi-msft]

Hi,

I think you should create the same amount of measures to define each field and apply to each field.

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft But if you use your measure with the switch function etc it also does not show the days in the past blue. In the other graph it does but I want it like this: 

Together= SWITCH(TRUE(); [PercentageHours] <0,8; "green"; [PercentageHours] <0,9; "orange"; [PercentageHours] <1; "red"; [PercentageHours] >1; "darkred"; MAX('Calendar'[Date]) <TODAY(); "blue")

But it is not showing the days in the past blue with this measure.

[amitchandak]

@Anonymous , you can explore other two option refer :

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting