topic Save value from a table in DAX Commands and Tips

Save value from a table

maserr — Mon, 11 May 2020 13:58:50 GMT

Hi there,

I am feeling pretty lost with DAX. I have a doubt I would know how to solve if the cell was a number, as I would store in a measure the value using CALCULATE(SUM(Value), Filter), but I do not know how to do it once the value I want to store is a varchar...

Value 1 Value 2 Value 3

X Y Z

A B C

I would want to have a measure that contains Value 1 = 'X' because I have chosen Value 3 = 'Z'

Thanks!

Re: Save value from a table

nandukrishnavs — Mon, 11 May 2020 14:29:11 GMT

maserr DAX measures are dynamic. We cannot store the value based on the previous selection.

If you select Value 3 = 'Z' below measure will return 'X'. But the moment you select another value or deselect the Value 3, result will change.

Val1 = SELECTEDVALUE(MyTable[Value1])

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: Save value from a table

kentyler — Mon, 11 May 2020 15:06:08 GMT

You say you want to "save" a choice. As the first reply pointed out there is no way to have anything like a global variable in DAX. However, you can put the possible selections into a separate table that has no relationships to the table you have your data in. This is an example of the DAX pattern "Parameter Table" https://www.daxpatterns.com/parameter-table/

Then when the user selects a value from the parameter table list, it will be available for you to use in your measures, and you can use it to lookup the corresponding value in your data table by using a function like LOOKUPVALUE(). Since the parameter table is no related to your data table any selection the user makes on the data table rows will not change the value selected in the parameter table.

Re: Save value from a table

maserr — Mon, 11 May 2020 15:26:54 GMT

I think I have not explained myself enough. I would want to achieve which is proposing the accepted solution from this post but with varchar.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measures-with-KPI/m-p/1078163#M15168

Thanks

Re: Save value from a table

kentyler — Mon, 11 May 2020 15:37:33 GMT

So, you wish to match a given value against a set of "target" values, but your problem is that the target values are strings instead of numbers... can you give us an example of the actual target values you want to use.

I think you will have to list all of your target strings. You probably not be able to use anything like, greater than or less than with strings. So your table of targets would be larger, but would work just by matching, using LOOKUPVALUE(), for example.