topic Re: Is it possible to store DAX operators as a string and recall them in an expression? in DAX Commands and Tips

Is it possible to store DAX operators as a string and recall them in an expression?

djallarii — Wed, 26 Jul 2023 18:24:01 GMT

We track metric actual values against metric target values and color code the actual values based on a pre-determined polarity. Some actual values are better if they are higher than the target and some actual values are better if they are lower than the target.

I'd like to store the DAX operators for each metric in a table and retrieve that operator in an IF statement when comparing values.

For example use something like this:

color_code = if ( metrics[ actual ] & polarity[ operator ] & metrics[ target ] , "red" , "green" )

To return this:

color_code = if ( 80 >= 75 , "red" , "green" )

With this result:

color_code = 80

Re: Is it possible to store DAX operators as a string and recall them in an expression?

AnthonyGenovese — Wed, 26 Jul 2023 21:22:03 GMT

Hey djallarii

So it won't work like that, but you can use your setup to get the output you are looking for. You would do something like this
var greaterthan = if ( metrics[ actual ]>= metrics[ target ] , "red" , "green" )
var lessthan = if ( metrics[ actual ]<= metrics[ target ] , "red" , "green" )
Var color_code = if (polarity[ operator ] =">=", greaterthan, lessthan)
return color_code
If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: Is it possible to store DAX operators as a string and recall them in an expression?

djallarii — Wed, 26 Jul 2023 22:03:04 GMT

Awesome, I understand what you're doing here but I'm curious if we can make it a bit more dynamic than hardcoding the colors.

Here's an example file:

Color Format.pbix

Can you apply your logic to achieve similar results using the polarity_ID to return the color formats from the color_format_true and color_format_false fields?

I'm thinking a SWITCH function using the polarity_ID could work where each expression and related operator is returned but I get duplicate results per metric.

Something like:
If polarity ID = 1 then evaluate SelectedActual > SelectedTarget, ColorTrue, ColorFalse
If polarity ID = 2 then evaluate SelectedActual < SelectedTarget, ColorTrue, ColorFalse
If polarity ID = 3 then evaluate SelectedActual <= SelectedTarget, ColorTrue, ColorFalse
etc.

Re: Is it possible to store DAX operators as a string and recall them in an expression?

AnthonyGenovese — Wed, 26 Jul 2023 22:13:26 GMT

I can't download the file, can you provide a table example in the reply? And how you want to use the measure? (What visual, and where)

Re: Is it possible to store DAX operators as a string and recall them in an expression?

djallarii — Wed, 26 Jul 2023 23:30:27 GMT

I uploaded the file to Google Drive and my personal OneDrive to try again:

Google Drive Link -

https://drive.google.com/file/d/1CbPcYgLduKUY47oUueflzIwEMTvlhS4r/view?usp=drive_link

OneDrive Link -

https://1drv.ms/u/s!AoXwZT-xo7jRgfUcCYqWcjoD6Divcw?e=ffxJBd

Otherwise here's the tables:

metric_table

metric_ID	metric_name	target	actual	polarity_ID
1	Metric 1	100	100	1
2	Metric 2	79	100	2
3	Metric 3	80	100	3
4	Metric 4	99	100	4
5	Metric 5	15	100	5
6	Metric 6	80	100	6

color_table

metric_ID	polarity	polarity_ID	color_format_true	color_format_false
1	>	1	Red	Green
2	<	2	Green	Red
3	<=	3	Yellow	Orange
4	>=	4	#00427a	#41bee8
5	!=	5	#ffd100	#80c342
6	=	6	#a05da5	Grey

The return statement in this measure should be dynamic if possible which is where the idea for a SWITCH statement came in.

Re: Is it possible to store DAX operators as a string and recall them in an expression?

djallarii — Thu, 27 Jul 2023 10:55:12 GMT

AnthonyGenovese I wanted to let you to know I was able to get the intended results using your original post and accepted it as the solution.

I am still curious if you can figure out a solution to my follow up example, however. If you have a chance to take a look, I would appreciate it!