Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure with different outputs based on condition

Hi   I want to create a measure that calculates based on a condition: If Table1[Column1] = "KPI1" then Formula1 else Formula2. The logic is very simple, but I can't get Power BI to read Table[Colu...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Anonymous Try:

    Measure =
  IF(
    MAX('Table1'[Column]="KPI1",
    DIVIDE(Sum(Num), Sum(Den), 0),
    DIVIDE(Sum(Num), Average(Den), 0)
  )
  • edhans's avatar
    edhans
    4 years ago

    Try this Anonymous 

    MeasureName =
VAR varValue =
    MAX( TableName[Field] )
RETURN
    IF(
        varValue = "KPI1",
        DIVIDE(
            SUM( Table[Num] ),
            SUM( Table[Den] ),
            0
        ),
        DIVIDE(
            SUM( Table[Num] ),
            AVERAGE( Table[Den] ),
            0
        )
    )

    You need to convert the field to a scalar value, which MAX does when used like this. MIN will give you the same result.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
4 years ago

Try this Anonymous 

MeasureName =
VAR varValue =
    MAX( TableName[Field] )
RETURN
    IF(
        varValue = "KPI1",
        DIVIDE(
            SUM( Table[Num] ),
            SUM( Table[Den] ),
            0
        ),
        DIVIDE(
            SUM( Table[Num] ),
            AVERAGE( Table[Den] ),
            0
        )
    )

You need to convert the field to a scalar value, which MAX does when used like this. MIN will give you the same result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Works very well!!

    Still intrigued why we need to work around it like that, doesn't seem like a efficient solution... PowerBI has it's problems, it shouldn't require a scalar.

     

    Anyway, thanks so much!

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Everything in DAX requires it to either be scalar, column, or table,. and are not interchangable.

      You cannot say IF(Table[Column] = "X", 1, 0) because in a measure it doesn't know how to process that column. It needs to be converted into a scalar value. SUM, MAX, AVERAGE, whatever.

      In a Calculated Column you can do that because those have Row Context. Measures do not. It is a very hard concept to learn right off the bat, but once you do it becomes clear. Row Context and Filter Context are two completely different animals. Measures understand Filter Context, not Row Context.

      There is another concept called Context Transition, and that is for another post. ğŸ˜‚

      Glad you have a solution!