rsQueryTimeoutExceeded

[jesushmmadrid]

Hello,

I am experiencing a critical performance and resource exhaustion issue when attempting to force the use of an inactive relationship in a DAX measure for invoicing/billing. The goal is for the date filters to affect the Invoice Date (FechaFactura) and not the Admission Date (Fecha), which is the default active relationship.

Data Model Context:

I have a model with three key tables:

1. Calendario (Date Dimension Table): Contains the FechaFactura column (Date).

2. HOSMA_Negocio (Admission Fact Table): Contains the Fecha column (Admission Date).

3. HOSMA_LineasFactura (Invoice Lines Fact Table): Contains the FechaFactura column (Invoice Date).

Table Relationships:

• Calendario[FechaFactura] (1) <-- (M) HOSMA_Negocio[Fecha] --> ACTIVE Relationship.

• Calendario[FechaFactura] (1) <-- (M) HOSMA_LineasFactura[FechaFactura] --> INACTIVE Relationship.

• Both fact tables (HOSMA_Negocio and HOSMA_LineasFactura) are related to each other via the IdEpisodio_TipoEA field (One-to-Many).

Problematic DAX Measure:

To switch the date context to the Invoice Date, I am using USERELATIONSHIP:

Facturacion = CALCULATE( COALESCE(SUM(HOSMA_LineasFactura[Importe_Rentabilidad]), 0), USERELATIONSHIP(HOSMA_LineasFactura[FechaFactura], Calendario[FechaFactura]) )

Issue and Errors:

When using this measure in any visual that involves filters from the Calendario table, the visuals fail to load and I get the following errors (even after simplifying tables):

1. In Power BI Desktop:

Error retrieving data for this visual. There's not enough memory to complete this operation. Please try again later when there may be more memory available.

1. In Power BI Service (Web):

Resources exceeded. The query exceeded the available resources. Try filtering to reduce the amount of data requested.

• Underlying Error: rsQueryTimeoutExceeded

• Details: The XML for Analysis request timed out before it was completed. Timeout value: 225 s.

What I have tried already:

• Simplifying the model and removing unnecessary columns.

• Reducing the granularity of the visuals (aggregating by month instead of by day).

Question:

Is this a symptom of a flawed data model due to having two potential date paths? Is there a more efficient DAX pattern to handle this context transition, or is the only robust solution to duplicate the Calendario table and create two separate active relationships?

Any suggestions for optimizing the model or the DAX are highly appreciated. Thank you in advance for your help!

[v-nmadadi-msft]

Hi @jesushmmadrid 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[jesushmmadrid]

Hi everyone,

Thank you very much for all your suggestions. I have tested the recommended solutions, including using two separate date tables and replacing USERELATIONSHIP with TREATAS, but the issue still persists and I continue hitting resource limits.

What I have tried:
1. Creating two separate date tables (AdmissionDate and InvoiceDate).
This improved performance, but it created a different problem. Several report pages combine metrics from both HOSMA_Negocio and HOSMA_LineasFactura using the same date slicers. With two date tables, slicers only filter one fact table unless I introduce bidirectional relationships or bridge tables, which causes ambiguity and performance issues. As a result, numbers between business and invoicing no longer match.
2. Replacing USERELATIONSHIP with TREATAS.
This reduces the load, but I still get timeouts when filtering large date ranges. I believe the indirect path through IdEpisodio_TipoEA forces very large filter contexts.
3. Simplifying the model and reducing columns.
Even with these optimizations, heavy visuals still fail.

What I need clarification on:

The core challenge seems to be that both fact tables are connected through IdEpisodio_TipoEA and are used together on multiple report pages. I need both fact tables to be filtered consistently, but invoicing measures must use a different date context, without breaking existing pages or duplicating the model entirely.

Before restructuring the whole model, I would like to ask:

Is there any recommended pattern for scenarios where two fact tables are connected, both need consistent filtering in some pages, but a subset of measures must use a different date path?

Would a calculation group, a disconnected date table with TREATAS, or a composite model pattern be more appropriate?

I appreciate any further guidance. It is a complex scenario and I would like to avoid breaking existing report logic.

Thank you again for your help.

[v-nmadadi-msft]

Hi @jesushmmadrid ,

Using a Composite Model can definitely help in separating the date logic while still keeping both fact tables consistently filterable, and applying group by/ summarize patterns in key measures can also reduce the size of the filter context and improve performance.

Please follow these articles for reference :
Optimization guide for Power BI - Power BI | Microsoft Learn

Data reduction techniques for Import modeling - Power BI | Microsoft Learn

Data reduction techniques for Import modeling - Power BI | Microsoft Learn

Regards

[v-nmadadi-msft]

Hi @jesushmmadrid 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Shai_Karmani]

It happens because USERELATIONSHIP over two fact tables and an indirect path forces the engine to evaluate a huge filter context.

The clean fix is to create two role-playing date tables, one for Admission and one for Invoice, each with an active relationship.

That removes ambiguity and makes DAX much faster. If model changes aren’t possible, try TREATAS instead of USERELATIONSHIP , it’s usually lighter on resources.

Shai Karmani | Data & Analytics

If it helped ,

please mark as resolved & give a kudo so others can find it too.

Let’s connect on LinkedIn

[DataVitalizer]

Hi @jesushmmadrid 

Your issue comes from having two date paths, the function USERELATIONSHIP makes the engine work too hard, which causes timeouts.

I suggest couple options to get it solved:

• Best option → use two separate Date tables (one for Admission, one for Invoice).

• Or replace USERELATIONSHIP with TREATAS, which is lighter.

• Also check cross‑filters between fact tables and pre‑aggregate invoice lines if they’re too detailed.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn