Need a replacement of SWITCH due to Resource Memory Exceeds.

[nimish_nagpal]

Background:

I have to switch between 55 measures and 16 dimensions at the same time in the query. My main query is:

MetricMeasure:=
CALCULATE( <Selected Measure>,
                         USERELATIONSHIP( Interface[Values],    <Selected Dimension>    )
)

in the above query, measure and dimension need to change in accordance with the selection on the report shown below.

Problem: This functionality is working fine with few measures and a few dimensions but with 55 measures and 16 dimensions my code is becoming 900 lines for code. 

Example Code of MetricMeasure with 2 measure and 2 dimensions looks like this. 

MetricMeasure:= 
SWITCH (
    SELECTEDVALUE ( Master_Dimension[Dimension] ),
    "Age Group", CALCULATE (
        SWITCH (
            SELECTEDVALUE( DIM_metric_center[Metric] ) ,
            "Total Overtime Cost", FACT_Payroll[Overtime],
            "Total Payroll Cost", FACT_Payroll[Total Payroll Cost],
            BLANK ()
        ),
        USERELATIONSHIP ( Interface[Values], Master_Age_Group[Age Group] )
    ),
    "Company", CALCULATE (
        SWITCH (
            SELECTEDVALUE( DIM_metric_center[Metric] ) ,
            "Total Overtime Cost", FACT_Payroll[Overtime],
            "Total Payroll Cost", FACT_Payroll[Total Payroll Cost],
            BLANK ()
        ),
        USERELATIONSHIP ( Interface[Values], DIM_company[LCC Code] )
    )
)

 Error Message: 

Any suggestion, how can I change the code in a way to use a nested Switch for 55 measure and 16 dimensions combination.

[Anonymous]

Maybe it's not about code but about your model. Try to find a better model.

Best
D