A table column Value impersonating \ representing a measure in tabular cube model

[Marbo]

Hi,

I'm struggling with something i think should be possible.

I have a table with a column representing the available measure names in my tabular cube model.

I have created a slicer based on this column. The next i would like is a matrix or chart presenting the measure selected in that slicer. Now i know of the solution to create a column with a switch but then i have to write down every single available measure.

What i want is to use the measure name from the table to present the selected measure so that when there are new measures defined within my model they are automaticaly available in the slicer en presentable in the matrix or chart.

Thanks in advance

[MFelix]

Hi @Marbo ,

You cannot get the created measure on your Power BI report in a automated way because the DAX engine does not have functions to get the dataschema.

If you refer to the blog post below you can check how you can get the measures information however this will not be dinamic so everytime you create a new measure you need to update this information.

https://exceleratorbi.com.au/getting-started-dax-studio/

To my knowledge don't think that the dinamic feature you want is available. Sorry for making this question but is your model going to change so many times and are you adding so many measures that you cannot track them down and change the switch measure as you need?

[Marbo]

Hi @MFelix

Thanks for the reply.

During development yes the model can change a lot. Sure you got i point of the amount changes further in the future.

But the main goal i always try to reach is that i create an environment for my customers where they don't need my anymore for complexe dax formulas and just do there desired analysis.

I have exactly that measure information and have a stored procedures within my SQL database to retrieve the latest model when ever i want. like hourly daily or weekly.

Do you think i could use the underlying measure expressions from that metadata to functionally get what i want?

regards

Marbo

[MFelix]

Hi @Marbo ,

If you are getting that information from the schemma maybve it's possile using the calculation groups that use the selectedmeasure to make the calculation.

Check the blog post below, probably you need to adjust the values from your dataschemma to pick the selected measures:

https://www.sqlbi.com/articles/introducing-calculation-groups/

https://www.youtube.com/watch?v=DqwcVF09rtA

https://www.sqlbi.com/calculation-groups/

As refered maybe somethings can be adapted from this to get the expected result.

[Greg_Deckler]

@Marbo - So are you saying that you have Measure1, Measure2, Measure3 and you have those in a column in a table and you want a measure that would be like:

Measure = MAX('MeasureTable'[MeasureName])

And it would return the calculation for which ever measure was selected?

If that is what you want then no. You cannot use EVALUATE in Desktop to the best of my knowledge.

You need to do the SWITCH thing with the disconnected table trick.

[amitchandak]

@Marbo ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
See if this video can help

https://www.youtube.com/watch?v=vlnx7QUVYME
Appreciate your Kudos.

[Marbo]

Hi @amitchandak

Thanks for your reply, I will try to explain myself a bit better.

In power BI desktop i have a connection to a tabular cube. There is a table met metadata about my cube model. Like my available measures. In power bi i added a measure column to that table with the follwing DAX statement

Measure Selection =

IF(ISCROSSFILTERED('SwitchMeasures'[MEASURE_NAME]),

SWITCH( TRUE(), VALUES('SwitchMeasures'[MEASURE_NAME]) = "Sum of AantalDagen", [Sum of AantalDagen], VALUES('SwitchMeasures'[MEASURE_NAME]) = "Sum of Aantal", [Sum of Aantal],

VALUES('SwitchMeasures'[MEASURE_NAME]) = "Sum of Bedrag", [Sum of Bedrag], BLANK()),BLANK())

Secondly i created a slicer based on the 'SwitchMeasures'[MEASURE_NAME] table column. Then i created a matrix with the new added measure and i can filter the matrix with the slicer to present 1 of the three measure written down in the switch statement.

Because the string value in the 'SwitchMeasures'[MEASURE_NAME] the table column is exactly the name of one of the measures in my model i thought why should i write the whole switch in detail, why not dynamically determine wich measure to show based on the name from the table column determined by the slicer.

i hope this helps explain a bit more.

Thanks