I’m having trouble creating a complex measure in PowerBI. To allow the user to filter at will and the output of the consolidated dashboard was updated.

I have survey data. Each question has a unique Row ID.
Table1 contains raw collected data (from 4 companies)
Table2 contains a list of unique questions with identical Row ID’s and also a column indicating how the data for the question should be consolidated.

The final consolidation has two stages of calculation:

1. Some basic operations like sum/count/max/median must be performed on the output of most questions.

2. About 80 questions need a complex calculation based on pre-calculated values of other questions (e.g. through the sum).

3. I solve by creating a calculated column (later also measure):

Consolidated Numeric Values = 
VAR CurrentRowID = (Table2[Row ID])
VAR AggRule = (Table2[Consolidation])
RETURN
IF(
    NOT ISBLANK(AggRule),
    SWITCH(
        AggRule,
        "sum", CALCULATE(SUM(Table1[Numeric values]), FILTER(ALL(Table1), Table1[Row ID] = CurrentRowID)),
        "median", CALCULATE(MEDIAN(Table1[Numeric values]), FILTER(ALL(Table1), Table1[Row ID] = CurrentRowID)),
        "max", CALCULATE(MAX(Table1[Numeric values]), FILTER(ALL(Table1), Table1[Row ID] = CurrentRowID)),
        BLANK() // Default case if no rule is found or for different values
    ),
    BLANK() // Return blank if AggRule is blank (no matching rule found)
)

2. I record from an external file the flow of operations that need to be performed to get the desired output. But here’s the thing. It’s tempting to use values from a column that have already been calculated, right? And create a new calculated column.

Special formulas = SWITCH(  TRUE(),
Table2[Row ID] = "OPERATIONAL.10", IFERROR( ( LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "OPERATIONAL.9") / IF(LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "OPERATIONAL.8") <> 0, LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "OPERATIONAL.8"), BLANK()) )  * 100, BLANK()),
Table2[Row ID] = "ESRS2.5", IFERROR( ( LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "ESRS2.3") /  ( IF(LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "ESRS2.3") <> 0, LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "ESRS2.3"), BLANK()) + LOOKUPVALUE(Table2[Consolidated Numeric Values], Table2[Row ID], "ESRS2.4") )  )  * 100, BLANK()),
BLANK() ) 

However, this cannot be used on a dashboard where I want to allow the user to use a slicer and simply filter by company, region, etc. Therefore I need to create a Measure and therefore not refer to the hard-calculated values in the column (p.1), but refer to the previously calculated Measure (p.1M), but how can I target individual Row IDs. If I’m getting this right, Measure doesn’t have an ID stored in it by which to target a specific value in DAX… Or at least I haven’t figured out how (even using all the AI tools I know).

So my question is if you have dealt with something like this and if so, what is the easiest way to proceed. I’m not sure if I can write a code that can calculate both the first aggregation (p.1) and the other more complex calculations (p.2) within one Measure.

Calculated Measure of p.1 (p.1M):

Consolidated Numeric Values M = 
VAR CurrentRowID = SELECTEDVALUE(Table2[Row ID])
VAR AggRule = SELECTEDVALUE(Table2[Consolidation])
RETURN
IF(
    NOT ISBLANK(AggRule),
    SWITCH(
        AggRule,
        "sum", CALCULATE(SUM(Table1[Numeric values]), Table1[Row ID] = CurrentRowID),
        "median", CALCULATE(MEDIAN(Table1[Numeric values]), Table1[Row ID] = CurrentRowID),
        "max", CALCULATE(MAX(Table1[Numeric values]), Table1[Row ID] = CurrentRowID),
        BLANK() // Default case if no rule is found or for different values
    ),
    BLANK() // Return blank if AggRule is blank (no matching rule found)
)

Thanks a lot!