Solved: Conditional Measure with IF

Anonymous · ‎09-09-2019

Hello,

I want to create a measure that calculates the % of execution based on some measure values. I also have a column called Status and I want to calculate with a IF clause:

IF opc[Status]="X"

THEN SUM(opc[Custos])*100/sum(opc[Orçamento])

ELSE 100

I'm trying:

If (opc[Status]="X";SUM(opc[Custos])*100/sum(opc[Orçamento]);100)

Power BI doesn't recognize the column opc[Status].

Anonymous · ‎09-09-2019

HI @Anonymous ,

You can't direct use columns in measure expressions, you need to add aggregate calculation on them to return summarized value and they can used in expressions.(e.g. sum, max, average, firstnonblank)
In addition, you can also use selectedvalue function to get current row contents value to compare with if statement conditions:

Measure =
VAR currStatus =
    SELECTEDVALUE ( opc[Status] )
RETURN
    IF ( currStatus = "X"; SUM ( opc[Custos] ) * 100 / SUM ( opc[Orçamento] ); 100 )

Regards,

Xiaoxin Sheng

View solution in original post

Anonymous · ‎09-09-2019

HI @Anonymous ,

You can't direct use columns in measure expressions, you need to add aggregate calculation on them to return summarized value and they can used in expressions.(e.g. sum, max, average, firstnonblank)
In addition, you can also use selectedvalue function to get current row contents value to compare with if statement conditions:

Measure =
VAR currStatus =
    SELECTEDVALUE ( opc[Status] )
RETURN
    IF ( currStatus = "X"; SUM ( opc[Custos] ) * 100 / SUM ( opc[Orçamento] ); 100 )

Regards,

Xiaoxin Sheng