Forum Discussion
expression language as in java a powerbi
Hello
If I have some columns of the database I store thresolds and colors like
| Thresold | Color |
| >0.9 | #00ff00 |
| >-0.8 && <-0.9 | #ffbf00 |
| <0.8 | #fb3318 |
Is it possible that you can analyze the column and use it in Measure for conditional formatting? With this I want to check by thresold and return the color.
4 Replies
kyrpav , I doubt expression but you can always create a color measure with cole name of hex code with prefix # and use in conditional formatting
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")https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
My case senario needs to check for specific data (like a metric) a thresold. These thresolds on specific occassions could change and this exists in db.
So i can not use static function hardcoding the thresolds cause in this case i will have to change the measure everytime an new case senario comes for a metric.
I need a way to make the function generic and to understand the thresold to make the comparison and return a value.
Hi kyrpav ,
You could use slicer to define the dynamic parameter.
Firstly, you need to create a new table to save your thresolds and use it as a slicer. Then create a measure like the following DAX:
Measure = VAR a = MINX ( ALLSELECTED ( 'thresolds' ), 'thresolds'[Value] ) VAR b = MAXX ( ALLSELECTED ( 'thresolds' ), 'thresolds'[Value] ) RETURN SWITCH ( TRUE (), SELECTEDVALUE ( 'Table'[Value] ) > b, "#00ff00", SELECTEDVALUE ( 'Table'[Value] ) <= b && SELECTEDVALUE ( 'Table'[Value] ) >= a, "#ffbf00", SELECTEDVALUE ( 'Table'[Value] ) < a, "#fb3318" )Put this measure into conditional formatting. Here is my result for your reference.
You are hardcoding the condition. I may not know which thresold has to be checked with is bigger, bigger or equal etc...
I am thinking of changind db to have different columns based on condition bigger ... and then use them one by one.
