Forum Discussion
conditional colouring by selection
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 |
Hi inf1948,
To achieve what you described you must follow these steps:
- Create a separate table that will have the period dictionary and use it in the slider.
- 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! 😀
3 Replies
Hi inf1948,
To achieve what you described you must follow these steps:
- Create a separate table that will have the period dictionary and use it in the slider.
- 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! 😀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
