Search code examples
if-statementreturndaxtabularmeasure

DAX if else for measure

How to use if else for DAX in the measure. If row value =1 then take the var a calculated value else take the var b calculated value

x:=var a=[DATA1] var b=[DATA2] return(if([HOUR]=1),a,b)

I get error using above formula

Solution

  • It seems your problem is that you are not aggregating the columns while creating the measure. Measures only works aggregating data in a given context, generally if you want to perform calculations per row you should use a calculated column instead of a measure.

    And the DAX expression for a calculated column should be:

    MyColumn = IF([HOUR] = 1, [DATA1], [DATA2])

    Otherwise if you want to use a measure you have to explicitely aggregate the column values in the given context, i.e:

    MyMeasure =
VAR a =
    FIRSTNONBLANK ( ExampleTable[Data1], 0 )
VAR b =
    FIRSTNONBLANK ( ExampleTable[Data2], 0 )
RETURN
    IF ( SUM ( ExampleTable[Hour] ) = 1, a, b )

    Or simply:

    MyMeasure =
IF (
    SUM ( [Hour] ) = 1,
    FIRSTNONBLANK ( ExampleTable[Data1], 0 ),
    FIRSTNONBLANK ( ExampleTable[Data2], 0 )
)

    Let me know if this helps.