Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Dynamic DAX

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

 

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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.