Weird Error when using a Calculation Group to Switch between different types of Aggregations

[WTreadway]

I have a Calculation Group created to change between Sum, Average, Min, and Max. 
The calculation Items are created like 'Sum = Calculate( Sumx(Table,SelectedMeasure())'

The Measure's are created like 'Total Duration = Calcuate(Sum(table[Total Duration])

When I put the calcuation group in a slicer and select any of the options I get this error. 

I've search for a while to get some idea what is causing this. The only YouTube video I've found using Calculation Group in a similar fashion works without any issue. I don't think I'm doing anything differently. Any help would be appreciated. 

[v-saisrao-msft · Accepted solution]

Hi @WTreadway,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Since your base measure Total Duration should be returning a numeric value, the issue might stem from the underlying column Table[Total Duration] being typed as DateTime in the data model, or from another measure (possibly DateTime-based) being affected by the calculation group. Check the data type of Table[Total Duration] in Power Query or the model view to confirm that it is a numeric type, such as Decimal Number or Whole Number. If it is currently stored as a DateTime, consider transforming it into a numeric format (e.g., total seconds or minutes) for aggregation purposes. 

For the calculation group items, Please try using explicit aggregations directly on the column, such as: 

Sum = CALCULATE(SUM(Table[Total Duration])) 
Average = CALCULATE(AVERAGE(Table[Total Duration])) 
Min = CALCULATE(MIN(Table[Total Duration])) 
Max = CALCULATE(MAX(Table[Total Duration])) 

This approach ensures you’re working with numeric outputs and helps preserve the slicer’s filter context properly. If your goal is to allow the calculation group to work across multiple measures, it’s best to use SELECTEDMEASURE() cautiously. In such cases, consider adding a safeguard like: 

IF( 
   ISSELECTEDMEASURE([DateMeasure1]) || ISSELECTEDMEASURE([DateMeasure2]), 
   BLANK(), 
   SELECTEDMEASURE() 
)

This will effectively exclude any DateTime measures from triggering errors in your visual.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-saisrao-msft · Accepted solution]

Hi @WTreadway,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Since your base measure Total Duration should be returning a numeric value, the issue might stem from the underlying column Table[Total Duration] being typed as DateTime in the data model, or from another measure (possibly DateTime-based) being affected by the calculation group. Check the data type of Table[Total Duration] in Power Query or the model view to confirm that it is a numeric type, such as Decimal Number or Whole Number. If it is currently stored as a DateTime, consider transforming it into a numeric format (e.g., total seconds or minutes) for aggregation purposes. 

For the calculation group items, Please try using explicit aggregations directly on the column, such as: 

Sum = CALCULATE(SUM(Table[Total Duration])) 
Average = CALCULATE(AVERAGE(Table[Total Duration])) 
Min = CALCULATE(MIN(Table[Total Duration])) 
Max = CALCULATE(MAX(Table[Total Duration])) 

This approach ensures you’re working with numeric outputs and helps preserve the slicer’s filter context properly. If your goal is to allow the calculation group to work across multiple measures, it’s best to use SELECTEDMEASURE() cautiously. In such cases, consider adding a safeguard like: 

IF( 
   ISSELECTEDMEASURE([DateMeasure1]) || ISSELECTEDMEASURE([DateMeasure2]), 
   BLANK(), 
   SELECTEDMEASURE() 
)

This will effectively exclude any DateTime measures from triggering errors in your visual.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[WTreadway]

I create another measure on a different field doing a count(records) and I got the same failure. I switched the measure to distinctCount(records) and get no error. Are the functions somehow causing this?

[v-saisrao-msft]

Hi @WTreadway,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @WTreadway,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @WTreadway,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @WTreadway,

Thank you for the detailed update and for confirming that your columns are decimals, not Date Time. The error ("A date value is outside of the transport protocol's supported range") likely arises because the Calculation Group’s SELECTEDMEASURE () is being applied to all measures in the visual context, including potentially incompatible ones that return Date Time values, which can cause type coercion issues during aggregations. 

This explains why DISTINCTCOUNT works—it simply counts unique values and avoids arithmetic operations that might lead to such errors, unlike SUM or AVERAGE. 

Try modifying your Calculation Items to include a safeguard using ISSELECTEDMEASURE () to ensure only numeric measures are processed: for the Sum item, use 

Sum Calculation Item:
IF(
    ISSELECTEDMEASURE([Total Duration]),
    CALCULATE(SUM(Table[Total Duration])),
    IF(
        ISSELECTEDMEASURE([Count of Records]),
        CALCULATE(SELECTEDMEASURE()),
        BLANK()
    )
)
Average Calculation Item:
IF(
    ISSELECTEDMEASURE([Total Duration]),
    CALCULATE(AVERAGE(Table[Total Duration])),
    IF(
        ISSELECTEDMEASURE([Count of Records]),
        CALCULATE(SELECTEDMEASURE()),
        BLANK()
    )
)

and apply similar logic for Min and Max, adjusting the aggregation logic as needed within the measure itself. 

check for any hidden Date Time fields in the visual, ensuring [Total Duration] is formatted as a "Decimal Number" in the Modeling tab, and testing in a new visual with only [Total Duration] to isolate the issue.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[WTreadway]

I have confirm all the datatype this is failing on are not dates. They a decmials. I found that is working on one measure that is doing a DistinctCount(Table[Key]).

The Measure themselves are exactly as you have suggested. And I double check the calc groups. There is not odd or off about them either.