Filtercontext with date relation

Hi,

I have the following Modell. I was trying to make a Matrix Table with all the Customers (Name) from DimKunde with a date relation between DimDate[Date] and DimKunde[Anlagedatum] and another filter with the Department from DimAbteilung[Abteilung]. For the value I took the net from the FactTable. The issue: if I use the department as a slicer the matrix visual will show up but if the slicer is set to all, the visual runs into an error (expression service limit).

The Visual has a filter, since I just want to show new customers related to the selected period and department, but the department should run for all as well, but it won't. The other two visuals are fine and following the filtercontext.

Neukunde Flag = 
CALCULATE(
    COUNTROWS(DimKunde),
    USERELATIONSHIP(DimDate[Date], DimKunde[Anlagedatum])
)

Maybe someone has a clue.
Kind regards

Sam

Need Help

vojtechsima
2 months ago
Hey, s_schultes ,

Your flag won't be filtered by department, and will send the result to every single department you have. This probably causes the overhelm of Direct Query.

Same also for customers that don't have a revenue or something:

You don't even need the flag. You can just build something like this, which will automatically not display anybody that doesn't have revenue or w/e you're after.

Neukunden Netto = CALCULATE( SUM(FactSales[geknetto]), USERELATIONSHIP( DimDate[Date], DimKunde[Anlagedatum] ) )

If you need to display customers by department, you can also do sometheing like this:

Neukunde Flag Updated = CALCULATE( COUNTROWS(DimKunde), USERELATIONSHIP( DimDate[Date], DimKunde[Anlagedatum] ), TREATAS( VALUES(FactSales[KundeId]), DimKunde[Id]) )

15 Replies

s_schultes
Frequent Visitor
1 month ago
I changed the structure. I made two separates columns in my DimKunde table (firstdatewithorder with id and department). Works way better now, even with userelationship. Thanks for your support, guess the size of the facttable breaks down the measure the whole time.
- v-achippa
  Community Support
  1 month ago
  Hi s_schultes,
  
  Thank you for the response and confirming that the issue is resolved now. Thank you for being part of Microsoft fabric community forum.
  
  Thanks and regards,
  
  Anjan Kumar Chippa
Fenil_Italiya
Advocate I
2 months ago
Hi, s_schultes Could you please share the details regarding which error its shows, either is shows the query resource exceed error or any other error?
Parchitect
Solution Sage
2 months ago
Could you please click “Details anzeigen” on the failed matrix visual and share the full technical error message?

The reason I ask is that the matrix works when one department is selected, but fails when Department = All. That makes it look like the visual/query becomes too expensive when the filter context is broad, but the exact error details would confirm whether this is a memory/resource issue or something else.
s_schultes
Frequent Visitor
2 months ago
Sure, Parchitect Fenil_Italiya
- Parchitect
  Solution Sage
  2 months ago
  Thanks for sharing the details. The error confirms that this is an expression service limit issue, not just a normal relationship/filtering issue.
  
  The reason it works when one department is selected but fails when Department = All is that the matrix has to evaluate the new-customer logic for a much larger customer set. The visual-level filter based on USERELATIONSHIP is probably being evaluated for many customer rows/cells, and that makes the generated DAX query too complex.
  
  Your USERELATIONSHIP logic is valid, but I would avoid using only a separate flag measure as the visual filter in this matrix.
  
  Try pushing the new-customer logic into the value measure itself:
  
  Neukunden Gesamtnetto = VAR IsNewCustomer = CALCULATE( COUNTROWS(DimKunde), USERELATIONSHIP(DimDate[Date], DimKunde[Anlagedatum]) ) RETURN IF( IsNewCustomer > 0, [Gesamtnetto], BLANK() )
  Then use this measure in the matrix instead of the regular [Gesamtnetto], and filter the visual where Neukunden Gesamtnetto is not blank.
  
  This keeps the logic in one measure and avoids asking the matrix to separately evaluate a flag filter plus the net measure for every customer.
  
  If it still hits the limit when Department = All, I would consider a model change instead of relying on USERELATIONSHIP in the visual:
  
  - create a separate role-playing Date table for customer creation date
  - relate that table actively to DimKunde[Anlagedatum]
  - use that date table for the “new customer” period selection
  
  Microsoft generally recommends active relationships whenever possible, because inactive relationships only propagate filters when activated inside DAX. USERELATIONSHIP is useful, but in large matrix visuals it can become expensive.
  
  So short version:
  - the error is caused by query/expression complexity when all departments are included
  - move the new-customer filter into the value measure
  - if the matrix is still too heavy, use a separate active date table for customer creation date
  - s_schultes
    Frequent Visitor
    2 months ago
    Hi Parchitect ,
    if I use your Dax and remove the flag from the filter context, I can select All from department....but it looks like the Date context is not really working. If i switch to department 43, there should be 42 customers...but I get way more. If I switch the department, the Ids are changing so I guess the time filter makes trouble at this point.
vojtechsima
Super User
2 months ago
Hey, s_schultes ,

Your flag won't be filtered by department, and will send the result to every single department you have. This probably causes the overhelm of Direct Query.

Same also for customers that don't have a revenue or something:

You don't even need the flag. You can just build something like this, which will automatically not display anybody that doesn't have revenue or w/e you're after.

Neukunden Netto = CALCULATE( SUM(FactSales[geknetto]), USERELATIONSHIP( DimDate[Date], DimKunde[Anlagedatum] ) )

If you need to display customers by department, you can also do sometheing like this:

Neukunde Flag Updated = CALCULATE( COUNTROWS(DimKunde), USERELATIONSHIP( DimDate[Date], DimKunde[Anlagedatum] ), TREATAS( VALUES(FactSales[KundeId]), DimKunde[Id]) )
v-menakakota
Community Support
2 months ago
Hi s_schultes ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank  Parchitect , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards,
Community Support Team
- v-menakakota
  Community Support
  2 months ago
  Hi s_schultes,
  
  I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.
  
  Best Regards,
  Community Support Team