Issue in SWITCH function

[MariaFlorenciaB]

i everyone, I need help with a formula:

I have this table (Jerarquia de medidas)

Level 1	Level 2
TON
GSV
OFF	Tactic
OFF	Manual
OFF	Tax

And I have in my data base, a formula for each measure: a formula for OFF, TON, GSV, Tactic, Manual and Tax.

So I need a formula that brings for each level,  the correct formula.

I have this:

Formula = 
VAR KpiList2 = SELECTEDVALUE('Jerarquia Medidas'[Level 2])
VAR KpiList1 = SELECTEDVALUE('Jerarquia Medidas'[Level 1])
RETURN
SWITCH(
    KpiList1;
    "TON"; [TON];
    "GSV"; [GSV Factura];
    "OFF"; [OFF];
    SWITCH(
        KpiList2;
        "Manual"; [Manual];
        "Tactic"; [TACTICO];
        "Tax"; [Tax]
    )
)

 The formula works for all the measures except If I add the line OFF to the formula. When I add OFF, I only see the total number of OFF for the measure OFF and for the measures Manual, Tactic and Tax.

What I need to see is for OFF the total number and for Manual, Tactic and Tax the subtotals.

Any ideas how I can solve this?

Thanks

[Anonymous · Accepted solution]

@MariaFlorenciaB - Try this - it changes the order of the switches, so that if there is a single value for level 2, it will show that value, otherwise, it will show the level 1 value:

Formula =
VAR KpiList2 =
    SELECTEDVALUE ( 'Jerarquia Medidas'[Level 2] )
VAR KpiList1 =
    SELECTEDVALUE ( 'Jerarquia Medidas'[Level 1] )
RETURN
    SWITCH (
        KpiList2;
        "Manual"; [Manual];
        "Tactic"; [TACTICO];
        "Tax"; [Tax];
        SWITCH ( KpiList1; "TON"; [TON]; "GSV"; [GSV Factura]; "OFF"; [OFF] )
    )

Cheers!

Nathan

[MariaFlorenciaB]

Thanks very much @Anonymous  !!!!