Search code examples
ssasdaxssas-tabular

a table of multiple values was supplied in 2012 SSAS tabular

I am having hard time using case statement in 2012 SSAS tabular using DAX. As 2012 has not introduced SWITCH statement.

My question: 

MeasureZ := IF(VALUES(Store[Close]) = "Y", MeasureX, MeasureY)

The above calculations returns values when I select "close" = "Y" or "N", if I select both its giving "a table of multiple values was supplied" error. Pls help me with this

Solution

  • Without a filter applied to Store[Close] of all of the values from this column will be returned by VALUES, leading to the error you received. Use FIRSTNONBLANK instead, which as the name implies returns the first value of the column in the current context that isn't blank.

    [MeasureZ ]: =
        IF (
            FIRSTNONBLANK ( Store[Close], 1 ) = "Y",
            [MeasureX],
            [MeasureY]
        )