dynamic column references

[as-nons-kk]

Hi, I have a table of 21 columns {id, metrics 1, metrics 2, ..., metrics 20}. I want to have 2 slicers in PBI report to select 2 metrics, then dynamically calculate min, max, and median in a table. Now I face the difficulty as DAX does not support dynamic column references directly. Any suggestion on this?

[Anonymous]

HI @as-nons-kk,

Perhaps you can take a look at the calculate group and field parameter features if they help with your scenario:

Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[shafiz_p]

@as-nons-kk From what I understand, you’d like the minimum, maximum, and median values to adjust dynamically based on slicer selections. For instance, when you choose ‘Metric 1,’ your table should display results specific to that ‘Metric 1’ column. To achieve this, consider unpivoting the ‘Metrics 1’ through ‘Metrics 20’ columns in your table using Power Query. This transformation will convert them into rows, simplifying dynamic referencing. Next, calculate the min, median, and max values based on the value column of unpivoted table and incorporate them into your table.

I hope this explanation helps! If it addressed your question, feel free to mark it as the solution!😊