Forum Discussion

s_schultes's avatar
s_schultes
Frequent Visitor
1 month ago
Solved

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 ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    1 month 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])
)

     

     

Parchitect's avatar
Parchitect
Solution Sage
1 month 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's avatar
s_schultes
Frequent Visitor
1 month ago

Parchitect Hi, i will try it with the second dateTable.

But how should the relation work now? 

I have DimDateKunde and DateKunde with a active Relation but if I make a relation with FactSales, this will be a inactive relationship. How should I handle this relation then? 

 

  • v-achippa's avatar
    v-achippa
    Community Support
    1 month ago

    Hi s_schultes,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Here you don't need to connect the new date table to FactSales, just create an active relationship between DimDateKunde → DimKunde[Anlagedatum].

    • DimDateKunde filters DimKunde by the customer creation date and your existing DimKunde → FactSales relationship carries that filter through to the sales table. Use DimDateKunde as the slicer for the new customer period and DimDate as the slicer for the sales period, this gives you two separate active date tables.

     

    Thanks and regards,

    Anjan Kumar Chippa