Choose DAX measure based on slicer value

Question

Is it possible to dynamically pick up appropriate DAX measure defined in a table by slicer value?

Source table:

+----------------+------------+
|      col1      |    col2    |
+----------------+------------+
| selectedvalue1 | [measure1] |
| selectedvalue2 | [measure2] |
| selectedvalue3 | [measure3] |
+----------------+------------+

The values of col1 I put into slicer. I can retrieve these values by:

SlicerValue = SELECTEDVALUE(tab[col1])

I could hard code:

MyVariable = SWITCH(TRUE(),
SlicerValue = "selectedvalue1" , [measure1],
SlicerValue = "selectedvalue2" , [measure2],
SlicerValue = "selectedvalue3" , [measure3],
BLANK()
)

But I do not want to hard code the relation SelectedValue vs Measure in DAX measure. I want to have it defined in the source table.

I need something like this:

MyMeasure = GETMEASURE(tab[col2])

Of course assuming that such a function exists and that only one value of col2 has been filtered.

If I understood you correctly and you want to execute a measure by its name stored in a dataset, then, as far as I know, it cannot be done. DAX code (measure) cannot be executed dynamically. — Nick Krasnov
I'm not sure this is possible. Can you give your use case? There may be a different approach that would work. — Alexis Olson
Something similar may be achieved by means of calculation groups and calculation items. But Power BI doesn't support them natively yet, it can only use them if they defined in Analysis Services if I'm not mistaken. So no, at the moment dynamic measure execution cannot be done this way. There is an open request (idea) for this feature to be implemented in PBI. So hopefully it will be soon — Nick Krasnov
Can't you just use a parameter table and base your measure off of the users' selection? — StelioK

greggyb greggyb · Accepted Answer · 2019-08-27T00:28:31

@NickKrasnov mentioned calculation groups elsewhere. To automate the generation of your hard-coded lookup table, you could use DMVs against your pbix.

You might do something like below to get output formatted that can be pasted into a large SWITCH.

SELECT
    '"' + [Name] + '", [' + [Name] + '],'
FROM $SYSTEM.TMSCHEMA_MEASURES

Choose DAX measure based on slicer value

1 Answers