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
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]
    )
)

 

  • Parchitect's avatar
    Parchitect
    Solution Sage
    1 month 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.