conditional colouring by selection

[Anonymous]

Hi community, 

I have the following requirements: 

- Period (numeric, not as date) as filter selection

- periods greater than selected period should be blue coloured

- periods less than selected period should be grey coloured

Well, I have a measure called #SelectedPeriod (e.g. 202003) and period-column #MyPeriodField

Now, I'm trying to tell PBI something like this: 

IF #MyPeriodField is less than #SelectedPeriod than "#01B8AA" else "#333333"

The problem is I can't create a measure because I'm unable to use column-values or rather non-aggregated values in measures

AND 

calculated colums are fixed and can't be used in conditional formatting. 

So, do you have an idea how I could get PBI to format my table by selected period? 

201801	201802	201803	201804	201805	201806	201807
54	456	45	546	456	86	456
42	12	454	21	454	121	6

[lkalawski · Accepted solution]

Hi inf1948,

To achieve what you described you must follow these steps:

1. Create a separate table that will have the period dictionary and use it in the slider.
2. Create a measure that will dynamically check whether the selected period is larger or smaller.

Measure = 
VAR _period = SELECTEDVALUE('Period'[Period])
VAR _tableperiod = SELECTEDVALUE('Table'[Period])
RETURN IF(_period > _tableperiod,0,1)

_period - dictionary, tableperiod - table with data

• Use this measure in Conditional formatting.

The result (I used background color, but you can also use this for font color):

_______________
If I helped, please accept the solution and give kudos! 😀

[Anonymous]

awesome, thank you!

[amitchandak]

Create a measure and then use that in the conditional format of a font under advance control choose the field and use this measure

Color Date = if(FIRSTNONBLANK('Date'[datekey],blank()) <="201803","black","blue")

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

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

Check steps

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