Forum Discussion

martti's avatar
martti
Icon for Helper I rankHelper I
6 years ago
Solved

Measure to calculate only for certain non-numeric values

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?

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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 

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      martti
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        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