[SOLVED] Measure DAX formula with SWITCH

Measure DAX formula with SWITCH

I want to create a formula for a measure that will apply different formulas depending on the FormulaId column in the fact table. But for some reason the line "Table'[FormulaId]" in "var Formula = 'Table'[FormulaId]" is highlighted in red with a syntax error. This column definitely exists, there is no mistake in the name.

Measure:=
var Formula = 'Table'[FormulaId]
return
SWITCH
(Formula,
       1,
             IF
             (
                    not (iserror(SUM('Table'[Value_1])/SUM('Table'[Value_2]))),
                    SUM('Table'[Value_1])/SUM('Table'[Value_2]),
                    BLANK()
             ),
       2,
             SUM('Table'[Value_1]),
       BLANK()
)

Solution

You cannot directly reference a column within a variable. Instead, you need to use functions like SELECTEDVALUE or VALUES to retrieve the value of a column within a measure :

Measure :=
var Formula = SELECTEDVALUE('Table'[FormulaId])
return
SWITCH(
    TRUE(),
    Formula = 1,
        IF(
            NOT(ISERROR(SUM('Table'[Value_1])/SUM('Table'[Value_2]))),
            SUM('Table'[Value_1])/SUM('Table'[Value_2]),
            BLANK()
        ),
    Formula = 2,
        SUM('Table'[Value_1]),
    BLANK()
)