Calculation Groups - incorrect result when applying via CALCULATE under certain conditions

[OwenAuger]

Under certain conditions, when applying a Calculation Item using the CALCULATE function, then performing an operation on the result (such as an arithmetic operation), incorrect values are returned.

Specifically, the issue appears to arise when:

• A Calculation Item returning a Double type is applied via CALCULATE to a measure returning an Integer type (or vice versa)
• An operation is performed on the result of the CALCULATE function (such as adding/subtracting a value).

I have attached a PBIX file demonstrating the issue.

Measures 1B & 2B return incorrect values, as described below:

Scenario 1:

1. Create a measure Zero Integer which returns a constant value zero of type Integer:
   Zero Integer =
   CONVERT ( 0, INTEGER )
2. ​Create a Calculation Group called MyCalcGroup with a Calculation Item called One Double defined as:
   CONVERT ( 1, DOUBLE )​
3. Create a measure that applies the One Double Calculation Item to the measure Zero Integer using CALCULATE:
   
   1A. Underlying Measure＝0 (Integer) | Calculation Item＝1 (Double) =
   CALCULATE ( [Zero Integer], MyCalcGroup[MyCalcItem] = "One Double" )
   This measure returns 1 as expected.
4. Create an otherwise identical measure with zero added:
   
   1B. Underlying Measure＝0 (Integer) | Calculation Item＝1 (Double) | Add Zero =

   CALCULATE ( [Zero Integer], MyCalcGroup[MyCalcItem] = "One Double" ) + 0
   This measure returns a value of approximately 4.6E+18, not as expected.

Scenario 2:

1. Create a measure Zero Double which returns a constant value zero of type Double:
   Zero Double =
   CONVERT ( 0, DOUBLE )
2. ​Create a Calculation Group called MyCalcGroup with a Calculation Item called One Integer defined as:
   CONVERT ( 1, INTEGER )​
3. Create a measure that applies the One Integer Calculation Item to the measure Zero Double using CALCULATE:
   
   2A. Underlying Measure＝0 (Double) | Calculation Item＝1 (Integer) =
   CALCULATE ( [Zero Double], MyCalcGroup[MyCalcItem] = "One Integer" )
   This measure returns 1 as expected.
4. Create an otherwise identical measure with zero added:
   
   2B. Underlying Measure＝0 (Double) | Calculation Item＝1 (Integer) | Add Zero =

   CALCULATE ( [Zero Double], MyCalcGroup[MyCalcItem] = "One Integer" ) + 0
   This measure returns a value of 0, not as expected.

This forum post discussed a particular instance of this issue.

Regards,

Owen Auger

Test Calculation Groups Bug.pbix

[OwenAuger]

This issue appears to be resolved as of the February 2021 Power BI Desktop update 🙂

[v-chuncz-msft]

@OwenAuger 

Glad to hear that. Thanks for sharing the solution.