Measure to calculate only for certain non-numeric values

[martti]

I have a measure that calculates a result from multiple different measures for different employees, e.g.

Measure0 = 
(
    [Measure1] +
    [Measure2]
) / 2 +
[Measure3]

My intent is for Measure3 to only be calculated if the employee belongs in a certain group. E.g.

Measure0 = 
(
    [Measure1] +
    [Measure2]
) / 2 +
IF(
    Tbl_employees[Group]="Group1" ;
    [Measure3] ;
    0
)

 But I cannot use the IF statement like that, it'd have to be like sum of groups in the employees table, but that is illogical. Is there a way to apply the measure calculation for only certain non-numeric values?

[MFelix · Accepted solution]

Hi @martti ,

When using measures you need to be aware that you cannot reference tables/column directly and you need to use aggregators in your case if you use the MAX function for the if statment that should work.

Measure0 =
 ( [Measure1] + [Measure2] ) / 2
    + IF ( MAX ( Tbl_employees[Group] ) = "Group1"; [Measure3]; 0 )

You can also try this variant of the calculation should work in the same way:

Measure0 =
( [Measure1] + [Measure2] ) / 2
    + CALCULATE (
        [Measure3];
        FILTER (
             Tbl_employees;
            Tbl_employees[Group] ="Group1"
        )
    )

Regards,

MFelix 

[martti]

I figured that referencing to the column directly would not be an option, but never thought about using MAX() for the reference. It worked exactly as intended, thanks!

[MFelix]

Hi @martti ,

You can also use SELECTEDVALUE, MIN, or other similar notation.

However not really sure how you want to present the information but if you want to have it in a table you need to use the second formula I presented since the sum of the values using the IF statement will get you an incorrect result due to context.

Regards,

MFelix