Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |