I’m trying to solve this problem for almost a week now.

I’ve got two Tables.
The first one (Table1) contains answers to questions from several entities.
The second (Table2) contains a unique list of questions (about 1500).

Each question is aggregated differently, depending on the nature of the data. Some are summed, median is calculated, maximum value is sought, etc.

The goal is to do the consolidation into Table2 so that the values calculated in this way can be displayed on the dashboard, the user can use filters, slicers, etc.

Please help.

That’s why I need something like this code:

Special Formulas Measure =
VAR Agg_E3_4_1_1 =
    CALCULATE(
        SWITCH(
            LOOKUPVALUE(Table2[Consolidation], Table2[Row ID], "E3-4_1.1"),
            "sum", SUM(Table1[Numeric values]),
            "median", MEDIAN(Table1[Numeric values]),
            "max", MAX(Table1[Numeric values]),
            BLANK()
        ),
        Table1[Row ID] = "E3-4_1.1"
    )
VAR Agg_ESRS2_2 =
    CALCULATE(
        SWITCH(
            LOOKUPVALUE(Table2[Consolidation], Table2[Row ID], "ESRS2.2"),
            "sum", SUM(Table1[Numeric values]),
            "median", MEDIAN(Table1[Numeric values]),
            "max", MAX(Table1[Numeric values]),
            BLANK()
        ),
        Table1[Row ID] = "ESRS2.2"
    )
VAR Agg_S1_9_1_6 =
    CALCULATE(
        SWITCH(
            LOOKUPVALUE(Table2[Consolidation], Table2[Row ID], "S1-9_1.6"),
            "sum", SUM(Table1[Numeric values]),
            "median", MEDIAN(Table1[Numeric values]),
            "max", MAX(Table1[Numeric values]),
            BLANK()
        ),
        Table1[Row ID] = "S1-9_1.6"
    )
VAR Agg_S1_9_1_5 =
    CALCULATE(
        SWITCH(
            LOOKUPVALUE(Table2[Consolidation], Table2[Row ID], "S1-9_1.5"),
            "sum", SUM(Table1[Numeric values]),
            "median", MEDIAN(Table1[Numeric values]),
            "max", MAX(Table1[Numeric values]),
            BLANK()
        ),
        Table1[Row ID] = "S1-9_1.5"
    )
VAR CurrentRowID = SELECTEDVALUE(Table2[Row ID])
VAR FinalResult =
    SWITCH(
        TRUE(),
        CurrentRowID = "E3-4_1.2", IF(NOT ISBLANK(Agg_ESRS2_2) && Agg_ESRS2_2 <> 0, (Agg_E3_4_1_1 / (Agg_ESRS2_2 / 1000000)) * 100, BLANK()),
        CurrentRowID = "S1-9_1.3", IF(NOT ISBLANK(Agg_S1_9_1_5) && Agg_S1_9_1_5 <> 0, (Agg_S1_9_1_6 / Agg_S1_9_1_5) * 100, BLANK()),
        BLANK()
    )
RETURN
    FinalResult

However, it doesn’t work, I tried an awful lot of variations, I have the feeling that DAX can’t handle multiple variables at the same time that are created within a single measure.

The logic is:
For a given RowID, go to Table2 and find there in the Consolidation column the rule according to which you count one value from the data in Table1 to specific RowID across all entities. Then, with the values ​​calculated in this way, you do a specific calculation, for example you divide them, multiply by 100, etc.