expression language as in java a powerbi

[kyrpav]

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.

[v-eachen-msft]

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.

[kyrpav]

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.

[amitchandak]

@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://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

[kyrpav]

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.