Forum Discussion

s_schultes's avatar
s_schultes
Frequent Visitor
2 months 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
    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])
)

     

     

Parchitect's avatar
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's avatar
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. 

  • Parchitect's avatar
    Parchitect
    Solution Sage
    2 months 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
      2 months 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]
    )
)

       

      • Parchitect's avatar
        Parchitect
        Solution Sage
        2 months ago

         Hi s_schultes,

         

        Good, that confirms the important part: the intersection logic is correct, because your test returns the expected 42 customers for department 43.

         

        In this case I would use the same logic not only as a count measure, but as the filter/gate for the matrix value measure.

         

        Try this version for the matrix value:

         

        Neukunden Gesamtnetto =
VAR CustomersWithSalesInCurrentContext =
    SELECTCOLUMNS(
        VALUES(FactSales[KundeId]),
        "KundeId", FactSales[KundeId]
    )
VAR NewCustomersInSelectedPeriod =
    SELECTCOLUMNS(
        CALCULATETABLE(
            VALUES(DimKunde[Id]),
            USERELATIONSHIP(DimDate[Date], DimKunde[Anlagedatum])
        ),
        "KundeId", DimKunde[Id]
    )
VAR CustomersToKeep =
    INTERSECT(
        NewCustomersInSelectedPeriod,
        CustomersWithSalesInCurrentContext
    )
RETURN
    CALCULATE(
        [Gesamtnetto],
        KEEPFILTERS(
            TREATAS(CustomersToKeep, FactSales[KundeId])
        )
    )

         

        Then use Neukunden Gesamtnetto in the matrix instead of the normal [Gesamtnetto], and filter the visual where Neukunden Gesamtnetto is not blank.

         

        The reason this should work better is that it explicitly keeps only customers that meet both conditions:

         

        - The customer was created in the selected period via DimKunde[Anlagedatum].
        - The customer also has FactSales rows in the current filter context, including selected year/month and department.

         

        Your previous measure already proved the right customer set. The issue was that the value measure also needs to be restricted to exactly that same customer set. Otherwise the matrix can show more customers than expected.

         

        Also important: USERELATIONSHIP only activates the inactive relationship for the duration of that calculation, so it is safer here to build the customer set first and then apply that set back to the fact table using TREATAS.

         

        If this still hits the expression service limit when Department = All, then I would move away from doing this dynamically in the visual and consider a model change, for example:

         

        - a separate role-playing Date table for customer creation date
        - or a precomputed new-customer/customer-period flag in Power Query or the source system

         

        But based on your debug result returning 42, I would test the measure above first.