Looking up invalid data on a dense spool

[powerbi-dev]

I have a problem with a published dataset, with the error message "Looking up invalid data on a dense spool". It happens with a single measure and only in PBI Service.

Datasource is a published Power BI Dataset. Source of dataset are dataflows.

The dataset was fine and this suddenly started to happen. When I'm using PBI Desktop, there are no errors, but I soon as I publish the data and connect my report via Live Connection, any visual with the measure returns the error. The measure is quite simple, just a SUMX with a simple CALCULATE filter, and it's used in many other measures of the report which do not return any error message.

I have already tried:

- Recreating the measure;

- Republishing the dataset;

- Deleting and republishing the dataset;

- Different workspaces;

- Creating a new file with PBIT template and connecting/

None worked. Sometimes, int he new file, I add a dimension and it stops working. I remove it and add again and it starts working, but suddenly stops again.

If anyone has had this problem in the past, I'd appreciate some advice, thank you.

[AmandaSaito]

I am facing the same problem but with Power BI application, I am facing this issue in two charts that don't have complex calculations, when they have blank data they display this "Looking up invalid data on a dense spool" message, weird part is that it only happens when the charts are blank and specific filters are applied to them, could this be an Power BI issue or does it sounds like I am doing something wrong?

[tsynenkyy]

The problem has been fixed, I checked my datasets and there is not this problem anymore.

[bogoLAC]

I experience same problem. My report connect live (not direct, not import) to published dataset.

The visual gives me an error in the report connected to the dataset but when i copy it and paste directly into the file with dataset, the problem goes away.

[v-yanjiang-msft · Accepted solution]

Hi @powerbi-dev @pkeefer @tsynenkyy ,

This issue has been submitted internally(ICM: 305645248). Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,
Community Support Team _ kalyj

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

[bogoLAC]

any update on the resolution?

[v-yanjiang-msft]

Hi @bogoLAC ,

Sorry the issue is still not be fixed, Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,
Community Support Team _ kalyj

[cdenniston]

Any update on this issue, I have noticed that when our report is republished to a My workspace we are not receiving the Looking up invalid data on a dense spool. Any thought that it has to do with the premium service? Is that why it is working in both the PowerBi desktop and a users My workspace? 

[bogoLAC]

I use Pro license and have this problem when report is published to Shared Work Space (not personal).

[bogoLAC]

let us know when the problem is going to be resolved, i still see the problem

[tsynenkyy]

HI Kalyj, do you have any news on this topic? we have an aoutage in prod, impacting our user community.

[v-yanjiang-msft]

Hi @tsynenkyy ,

Sorry for the inconvenience, the engineers are rushing to fix this known bug, the fix will come soon, thank you for your patience. If there is any news, I will update it here.

Best Regards,
Community Support Team _ kalyj

[tsynenkyy]

Hi,

I have exactly the same problem, last week everything was OK and from yesterday (monday 02/05) one of my reports have this error, but only one of all my reports   , all the other ones are OK.

I hope someone can help us

[tsynenkyy]

Does anyone know how to open a ticket directly to microsoft? maybe this is an issue the should check and fix

[pkeefer]

We started receiving this error yesterday as well.  It sounds like a similar scenario to yours:  CALCULATE with a relatively simple filter; only deleting the offending measures made the error go away.

Not all measures were causing problems, only a small number of them that had more than one logical operator, e.g.

age_1 = 
var maxDate = MAXX(ALLSELECTED(Payables), Payables[document_date])

return CALCULATE
(
    SUM(Payables[balance_amount_dc]),
    FILTER
    (
        Payables,
        var docAge = DATEDIFF(Payables[document_date], maxDate, DAY)

        return Payables[document_amount_dc] <> 0 && docAge >= 31 && docAge <= 60
    )
)

Other, very similar measures, are working fine, e.g.

age_0 = 
var maxDate = MAXX(ALLSELECTED(Payables), Payables[document_date])

return CALCULATE
(
    SUM(Payables[balance_amount_dc]),
    FILTER
    (
        Payables,
        var docAge = DATEDIFF(Payables[document_date], maxDate, DAY)

        return Payables[document_amount_dc] <> 0 && docAge <= 30
    )
)

Since this is an aging report, I was able to work around it by simplifying filter conditions in the later buckets and just subtracting the earlier buckets.

There are only two tables - both imported from SQL.  The table with the measures causing issues has around a million rows.

[TomMartens]

Hey @powerbi-dev ,

can you please share what you exactly mean by "live connection".

What is the data source for the Power BI report you are publishing.?
As you are using the term "live connection" I assume it's either

• SQL Server Analysis Services Multidimensional on-premises
• SQL Server Analysis Services Tabular on-premises
• Azure Analysis Services
• Power BI dataset

If you are connecting to a Power BI dataset, can you please provide the information if the dataset is a composite model, meaning sourcing data from at least one different Power BI dataset (Use composite models in Power BI Desktop - Power BI | Microsoft Docs)

Regards,

Tom