Dynamic DAX

[Anonymous]

Hello,

Is there a way, just like DynamicSQL to retreive a measure by its string name.

I'll explain a bit more.

In our model we have over 180 KPI's, even more measures to build the KPI's (Wich are only visible measures).

We have a measure table that hold what measures could be selected on what report/page/visual.

We want our users to be able to select what measure they want to display on the visuals.

Now this is a huge switch, but I would like to create it dynamical, as the codes in the KPI-table are exactly the measure names ...

Si instead off (this old statement only works for 1kpi selected).

KP1 = 
SWITCH (
    TRUE(),
values(tbl_KPI[KPI]) = "01. # Material units", [KPI_MAT_NBR_ENGINE1],
values(tbl_KPI[KPI]) = "02. Material kms", [KPI_MAT_KM_MAT1],
values(tbl_KPI[KPI]) = "03. Train kms", [KPI_MAT_KM_TRAIN1],

We would like to have statement like (i'll put the statement in pseudo)

KPI1 = For the First Selected Measure_Name in the KI Table display the measure with the Measure_Code in that table, if No First Selected Element display BLANK()

KPI2 = For the Second Selected Measure_Name in the KI Table display the measure with the Measure_Code in that table, if No Second Selected Element display BLANK()

We would display up to 5 KPI's in one Chart.

Kind regards, Harry

[v-juanli-msft]

Hi @Anonymous

You add the “KP1” measure to a visual, then when selecting among measure name (eg. 01. # Material units) this visual will show the value calculated by “KPI_MAT_NBR_ENGINE1” measure, right?

“this old statement only works for 1kpi selected”

“We would display up to 5 KPI's in one Chart”

Actually, I can’t make myself clear about what’s you are looking for.

I find a pbix similar to your description, is this relevant to your situation?

Best Regards

Maggie

[Anonymous]

I have it working, 5 selected KPI's (measures) on a chart.

But the main goal would be to create the switch dynamic.

Now we have all our KPI's listed (fortunalty they come from a database and a script generates to switch, just copy paste).

But it would be great to have something like in excel INDIRECT

So instead of (this is a reduced one, our biggest has 160 lines)

KPI_2_P:= SWITCH(TRUE(),
    [KPI_2_P_CODE] = "KPI_MAT_KM_Pct_PAV_TOTAL", [KPI_MAT_KM_Pct_PAV_TOTAL],
    [KPI_2_P_CODE] = "KPI_MAT_DUR_Pct_PAV_COMMPAV", [KPI_MAT_DUR_Pct_PAV_COMMPAV],
    [KPI_2_P_CODE] = "KPI_MAT_KM_Pct_PAV_MAT", [KPI_MAT_KM_Pct_PAV_MAT]
)

to have something like

KPI_2_P:= IF(ISBLANK([KPI_2_P_CODE]),BLANK(), INDIRECT([KPI_2_P_CODE]))

Where the INDIRECT users the text in KPI_2_P_CODE and fetches/executes the DAX composed in that, in our case the measure.