Report failing because of calculated table expression error

[PauSe]

Hi all. I have a report that reads data from an Oracle platform, hosting SAP data and subject to twice-daily refreshes. Within the report I have a calculated-table, written in DAX, which uses / enhances data from the Oracle tables. I published this to a workspace (to much applause from grateful users!) but, since the first data refresh, I receive an error message:

The query referenced calculated table 'ct_LookUp_FreightCost' which does not hold any data because there is an error in its expression.

I cannot see an error in the table's expression; it uses ADDCOLUMNS wrapped around SELECTCOLUMNS, SUMMARIZECOLUMNS and of course Filters, It was developed and tested with DAX Studio and works without issue in Power BI desktop. It also works when the report is first published. It only seems to fail during a nightly refresh. I will also mention that this is not the first report in which I use a DAX-scripted, calculated-table in reports. They are running on the same platform and workspace and I am not having errors with those (so maybe the error message is correct!)

As far as I know it is not possible to control the order of the refresh of data sources but I assumed that calculated-tables would be refreshed when the report is loaded. Any ideas how I can debug this problem?

[PauSe]

Update! My script is now failing in DAX Studio (which is strange because it worked there before). The message (translated) is "SummarizeColumns() and AddMIssingItems() may not be used in this context." 

I've included the script below and I have isolated the area which is failing. Any ideas on how to rewrite the clause welcomed!

EVALUATE

ADDCOLUMNS(

    SELECTCOLUMNS(

        SUMMARIZECOLUMNS(

            FCT_RCP_SHIPING_COST[cc_getRoute]
            , FCT_RCP_SHIPING_COST[DELIVERY_DOCUMENT]
            , DIM_RCP_DELIVERY[cc_getReleaseQuantity]


// !!!   It is the SUMMARIZECOLUMNS within the FILTER below that causes the script to fail  !!!

            , FILTER(FCT_RCP_SHIPING_COST, FCT_RCP_SHIPING_COST[DELIVERY_DOCUMENT] IN
                    SELECTCOLUMNS(
                        SUMMARIZECOLUMNS(
                            DIM_RCP_DELIVERY[ROUTE]
                             , "lastDeliveryNumber", MAX(DIM_RCP_DELIVERY[DELIVERY_NO])
                            )
                        , "DELIVERY_NO", [lastDeliveryNumber]
                        )
                    ) // end FILTER


    , "avg_FreightCosts", CALCULATE(
                            AVERAGE(FCT_RCP_SHIPING_COST[NET_VALUE])
                            , FILTER(FCT_RCP_SHIPING_COST, FCT_RCP_SHIPING_COST[DELIVERY_DOCUMENT])
                            )        
            )   // end SUMMARIZECOLUMNS

    // rename columns (SELECTCOLUMNS)
        , "Route", [cc_getRoute]
        , "Delivery_Document", [DELIVERY_DOCUMENT]
        , "Release Quantity", [cc_getReleaseQuantity]
        , "Freight Cost", [avg_FreightCosts]
        ) // end SELECTCOLUMNS

// calculate the rate per ton (ADDCOLUMNS)
    , "Rate per Ton", DIVIDE([Freight Cost], [Release Quantity])
)

[PauSe]

Thankyou Zhengdong Xu for your reply. I followed your advice and the report successfully refreshed in Desktop.  I have reviewed the DAX script for the calculated table, made a few changes that improve performence. We republished and unfortunately, after the night refresh, the report is failing with the same message. 

Thanks also for the article. I had already seen this and its good information. I'm not using Direct Query so the first case should apply. 

I am going to refer this problem to our IT organisation because I have no visibilty to the logs. But thank you again for taking the time to respond,

[v-zhengdxu-msft]

Hi @PauSe 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PauSe]

Hi, no unfortunately I cannot solve this. My interim solution is to run the script in DAX Studio against the data set, export the results to Excel and use the Excel as a data source. This is not optimal as there is no automation. 

[v-zhengdxu-msft]

Hi @PauSe 

Open your report in Power BI Desktop. Click the “Refresh” button to verify if the error occurs during manual refresh, you said that it only fail during a nightly refresh, so I'm surmising that maybe it's the timing of the refresh that conflicts with other refresh items, if so, please consider changing the schedule refresh time.

Ensure that there haven't been any changes in the Oracle data structure or in the SAP data that's being pulled. Sometimes, changes in the underlying data can cause calculated tables to fail if they rely on specific columns or data types.

Although the DAX expression works in Power BI Desktop and DAX Studio, it's worth revisiting the expression to ensure it doesn't use any functions or patterns that might behave differently during a refresh cycle. For example, certain DAX functions might behave differently when executed in a DirectQuery context versus import mode. 
Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.