Create a dynamic measures using field parameter measure values

[frankGB]

I am using field parameters for measures in Power BI. I use a simple paramter table as a sample. The actual paramter table has more than 100 metrics.

Referenced Measures used in the Field Paramter are:

Sales = SUM('Table-Sales'[Sales])

Units = SUM('Table-Units'[Units])

Measure-Parameter

Let's use two simple tables for Sales and Units. Both Tables are connected to a 'Date-Table'.

Table-Sales

Table-Units

Based on the set-up above, this is what I would like to accomplish using the parameters. 

Calculated Measure  = CALCULATE(SUM('Measure-Parameter'[Metric Fields]), 'Date-Table' =  Date(2022,01,01)

The expected visual output of this simple example using the Metric column from the parameter table would then be:

This formular obviously doesn't work because SUM requires a value column as a reference.  

Buit how could this work? "Metrics Field" column value would be exactly the syntax that would be needed for the calculated measure but yes, it would have to be provided as a variable in the SUM function.  

I don't want to use a Switch Function because it would require to incldue every single measure used in the parameter to be listed there. With over 100 parameter measures this will not scale and be very difficult to maintain moving forward.  

[amitchandak · Accepted solution]

@frankGB , Not very clear. One it simple Switch values on Row in Matrix Visual

https://www.youtube.com/watch?v=M5PvQUy-L_4

If not then you need to use the calculation group

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display: https://youtu.be/qMNv67P8Go0

[frankGB]

The calculation groups seem to work. The video is quite useful. 

Thank you for pointing me to this solution.

[frankGB]

Thank you Amit,

the Matrix visual works if you don't use any calculations. But because in the example I need a filtered calculation using the calculated measure, it will not work when the calculation becomes more complex. 

If I for exaple want to calculate a change percentage, I will need a calculated measure like below.

Calculated Measure  = CALCULATE(SUM('Measure-Parameter'[Metric Fields]), 'Date-Table' =  Date(2022,01,01) / 

CALCULATE(SUM('Measure-Parameter'[Metric Fields]), 'Date-Table' =  Date(2022,01,02)

I had a quick look at the Calculation Group. This might be a feasible solution actually. Let me try this. It seems using Calculation Groups and using dynamic period calculation groups, would remove the need to the field paramters anyway.