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...
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 

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