SUMX breaks model - Table within measure can populate (verified in DAX Studio), but unable to sum

[Anonymous]

I have a measure that I have validated is working correctly.  I need to sum it over the dataset so I have a measure I wrote that groups the table to the granularity required for the measure to produce the result set I want:

AggregatedRetailError% = 
SUMX(
    ADDCOLUMNS(
        GROUPBY(
            ShipmentHistory
            ,ShipmentHistory[PartnerID]
            ,ShipmentHistory[ShipCarrierServiceID]
            ,ShipmentHistory[Zone]
            ,ShipmentHistory[ParcelTypeID]
            ,ShipmentHistory[ParcelTypeIDOrig]
            ,ShipmentHistory[WeightLbs]
            ,ShipmentHistory[IsCubic]
            ,ShipmentHistory[ParcelCube]
            ,ShipmentHistory[StartDateYear]
            ,ShipmentHistory[PartnerRate]
            ,ShipmentHistory[ClientCarrierServiceZonePacktype]
        )
        ,"ErrorPct",[PartnerRateError%]
    )
    ,[ErrorPct]
)

The calculation, I know, works correctly.  If I load it into DAX Studio and run up to ADDCOLUMNS the values come out as expected.  The column loads and populates with all the values:

However, as soon as I add the SUMX in Power BI Desktop, it breaks the report.  The error message is less than helpful and if I click "Copy Details" the message is all just the schema around the report, nothing about how the error actually occured:

I can't see any reason why the SUMX would fail - the summay table works, the column populates, but the report won't do anything with it.  To make it more confusing, this is nearly identical measure/model to an existing one I have that works exactly as expected.

[v-juanli-msft]

Hi @Anonymous 

What kind of data sources and connection type you use?

If it is direct query/live connection, there are some limitations when creating DAX query.

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=asallproducts-allversions

You could try to create simple measures with every single DAX function and check which breaks the formula.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-juanli-msft  - I have simplified all my measures and it's the exact same logic, calculations, and processing order as an existing report where they work perfectly.  The models are both the same (composite due to aggregations), both SQL Server sources.  The main tables in question:

ShipmentHistory is Direct Query with 2 Imported Agg tables behind the scenes with direct matches to these groupings

ShipCarrierRatesQuery is an import table.

Calculation flow is two measures to calculate two different rates, then one final measure to get the smaller of the two.

CurrentClientNotCubic = 
var weightShip = MINX(ShipmentHistory,[WeightLbs])
var weightRate = MAX(ShipCarrierRatesQuery[Weight])
var weightfinal = MINX(FILTER(ShipCarrierRatesQuery,ShipCarrierRatesQuery[Weight]>=MIN(weightRate,weightShip)),[Weight])
return 
MINX(
    FILTER(
        ShipCarrierRatesQuery
        ,ShipCarrierRatesQuery[Weight] = weightfinal
    )
    ,[ClientRate]
)

CurrentClientCubic = 
var cubeRateTable = SUMMARIZE(
    FILTER(
        FILTER(
            ShipCarrierRatesQuery
            ,ShipCarrierRatesQuery[IsCubic] = TRUE()
        )
        ,ShipCarrierRatesQuery[Cube]>0
    )
    ,ShipCarrierRatesQuery[Cube]
    ,ShipCarrierRatesQuery[ClientRate]
)
var cubeShip = CALCULATE(
    MIN(ShipmentHistory[ParcelCube])
    ,KEEPFILTERS(ShipmentHistory[ParcelCube] > 0)
)
var cubeFinal = IF(
    COUNTROWS(cubeRateTable)=1
    ,MINX(cubeRateTable,[Cube])
    ,cubeShip
)
RETURN
MINX(
    FILTER(cubeRateTable
        ,[Cube]=cubeFinal
    )
    ,[ClientRate]
)

After these calcs, this determines the final rate:

CurrentClientBaseRate = 
var cubic = [CurrentClientCubic]
var notcubic = [CurrentClientNotCubic]
return
IF(
    ISBLANK(cubic)
    ,notcubic
    ,MIN(cubic,notcubic)
)

If I have the measure on a table that is fully split out (i.e. visual has all the group by columns and measures), it works mostly - I still get that error but not nearly as frequently.  If I try to use the grouped measure on a high level (i.e. has only one of the group columns in the visual) I cannot get it to render at all.

The final group by measure is in the initial comment, but added here again (I altered the name of some of these since the initial post):

AggregatedRetail = 
AVERAGEX(
    ADDCOLUMNS(
        GROUPBY(
            ShipmentHistory
            ,ShipmentHistory[PartnerID]
            ,ShipmentHistory[ShipCarrierServiceID]
            ,ShipmentHistory[Zone]
            ,ShipmentHistory[ParcelTypeID]
            ,ShipmentHistory[ParcelTypeIDOrig]
            ,ShipmentHistory[WeightLbs]
            ,ShipmentHistory[IsCubic]
            ,ShipmentHistory[ParcelCube]
            ,ShipmentHistory[StartDateYear]
            ,ShipmentHistory[PartnerRate]
            ,ShipmentHistory[ClientCarrierServiceZonePacktype]
        )
        ,"FinalRate",[CurrentClientBaseRate]
    )
    ,[FinalRate]
)

[Anonymous]

@v-juanli-msft  I should mention that each of those 3 measures, will work and calculate anywhere I use them.  Additionally, the CurrentClientCubic and CurrentClientNotCubic (the two initial measures) WILL work in the grouping measure.  The final one will not. For example, both of these work:

AggregatedRetail = 
AVERAGEX(
    ADDCOLUMNS(
        GROUPBY(
            ShipmentHistory
            ,ShipmentHistory[PartnerID]
            ,ShipmentHistory[ShipCarrierServiceID]
            ,ShipmentHistory[Zone]
            ,ShipmentHistory[ParcelTypeID]
            ,ShipmentHistory[ParcelTypeIDOrig]
            ,ShipmentHistory[WeightLbs]
            ,ShipmentHistory[IsCubic]
            ,ShipmentHistory[ParcelCube]
            ,ShipmentHistory[StartDateYear]
            ,ShipmentHistory[PartnerRate]
            ,ShipmentHistory[ClientCarrierServiceZonePacktype]
        )
        ,"FinalRate",[CurrentClientCubic]
    )
    ,[FinalRate]
)

AggregatedRetail = 
AVERAGEX(
    ADDCOLUMNS(
        GROUPBY(
            ShipmentHistory
            ,ShipmentHistory[PartnerID]
            ,ShipmentHistory[ShipCarrierServiceID]
            ,ShipmentHistory[Zone]
            ,ShipmentHistory[ParcelTypeID]
            ,ShipmentHistory[ParcelTypeIDOrig]
            ,ShipmentHistory[WeightLbs]
            ,ShipmentHistory[IsCubic]
            ,ShipmentHistory[ParcelCube]
            ,ShipmentHistory[StartDateYear]
            ,ShipmentHistory[PartnerRate]
            ,ShipmentHistory[ClientCarrierServiceZonePacktype]
        )
        ,"FinalRate",[CurrentClientNotCubic]
    )
    ,[FinalRate]
)

Another tidbit - if I return the variables independently in the grouping measure (example below), the variables all return and calculate.  For example, if I alter the CurrentClientBaseRate (the final measure that I want to use in the grouping measure), to only return variable values, it works:

CurrentClientBaseRate = 
var cubic = [CurrentClientCubic]
var notcubic = [CurrentClientNotCubic]
return
cubic

CurrentClientBaseRate = 
var cubic = [CurrentClientCubic]
var notcubic = [CurrentClientNotCubic]
return
notcubic

If I have CurrentClientBaseRate as either of those two calculations, then the final grouping measure will calculate.

Hopefully that makes sense...

[amitchandak]

Can you share sample data and sample output.

[Anonymous]

Apologies for the dealy - I was out of town.  I have since opened a ticket with microsoft for this issue after looking at it with one of their MVPs.