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

    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. 

    • Parchitect's avatar
      Parchitect
      Solution Sage
      1 month ago
      Before changing the DAX or other changes further, I think we need to clarify one point because it changes the measure logic.

       

      Should the selected Year/Month filter only DimKunde[Anlagedatum] to identify new customers, or should it also filter FactSales[Datum] when calculating [Gesamtnetto]?

       

      For example, for Year = 2026 and Department = 43, you mentioned the expected result is 42 customers.

       

      Are those 42 customers:
      1. customers created in the selected period and having at least one FactSales row in department 43, regardless of sales date;
      or
      2. customers created in the selected period and having FactSales rows in department 43 within the same selected sales period?

       

      That distinction is important because the date table can affect DimKunde through USERELATIONSHIP, while department comes through FactSales. If both date paths are active in the calculation, the measure can return more or fewer customers than expected.

       

      I would also test this debug measure for Department 43:

       

      [Debug Intersection] =
      VAR NewCustomersByCreatedDate =
          CALCULATETABLE(
              VALUES(DimKunde[Id]),
              USERELATIONSHIP(DimDate[Date], DimKunde[Anlagedatum])
          )
      VAR CustomersWithBeleg =
          CALCULATETABLE(
              VALUES(FactSales[customerid])
          )
      VAR CustomersToShow =
          INTERSECT(
              NewCustomersByCreatedDate,
              CustomersWithBeleg
          )
      RETURN
          COUNTROWS(CustomersToShow)

       

      Please replace FactSales[customerid] with your actual customer key column.

       

      If this returns 42 for Department 43, then the customer filtering logic is correct and we only need to adjust the value measure. If it returns more than 42, then the department/date filter is not intersecting with the customer set as expected.
      • s_schultes's avatar
        s_schultes
        Frequent Visitor
        1 month ago

        Hi Parchitect ,

        good Point, I was aiming to this solution you mentioned already:

        2. customers created in the selected period and having FactSales rows in department 43 within the same selected sales period?

         

        I tested the measure and it returns 42 in department 43. I used a similar measure for my card visual and diagramm.

         

        Neukunden mit Beleg = 
CALCULATE(
    DISTINCTCOUNT(DimKunde[id]),
    USERELATIONSHIP(DimDate[Date], DimKunde[Anlagedatum]),
    TREATAS(
        VALUES(FactSales[KundeId]),
        DimKunde[id]
    )
)

         

  • 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