DAX measure to count active cases within date filter context

[AGuldbrandt]

Hi,

In relation to performance optimization of a tabular data model I wonder if anyone has any DAX solution for the following example that I would try to explain:

In the example I am giving to explain the issue we have a fact table with Customer Support Cases.

The table consists of two customer cases. First customer case was created on the 01-01-2023 (DD-MM-YYYY) and closed on the 06-01-2023 (customerkey 1). The other customer case was created on 05-01-2023 and closed on the 10-01-2023 (customerkey 2). 

I am interested in a design where the user of the data model at any giving time (date filter context) can see the number of distinct customers with at least one active case. 

In my original design the fact table looks like the example given below:

This design enables me to make a DAX measure that counts distinct CustomerKeys. If a user of the data model then looks up the number of active customers on the date 05-01-2023, the result presented to the user would be 2 active customers. Because a datekey reference exists for both customers on this given date. This design provides the exact functionality I am looking for, the only issue is that as the model grows, more and more customers will appear and some of them might even end up having very long case handling times. This will increase the size of my data model and with time, I will end up with a slow model.

The design I am pursuing looks like the fact table given below:

I am interested in reducing the number of rows in my fact table, without losing any of the functionality from the previous design. This will ensure that performance of measures such as "Number of created Customer Cases" and "Number of Closed Customer Cases" will improve in performance due to fewer rows in the fact table.

Only issue is, I still wanna be able to have a measure that shows me that 2 customers had an active case on the date 05-01-2023. Therefore, what I need is a measure that can utilize the ValidFrom and ValidTo date keys in relation to my Date Dimension. I am aware that I can not use the normal relationsship with the date dimension, since I no longer have a key reference for Customerkey 1 on the 05-01-2023. So, whenever I chose any date or series of date in my date dimension, the measure should be able to detect which fact rows are within the given filter context and then count the distinct number of customerkeys.

I hope what I have tried to explain above makes sense and I really hope someone might have a solution for the issue I am currently facing.

Thank you!

[Anonymous]

Hi @AGuldbrandt ,

I think your Fact table could be easier than the one in your screenshot.

I suggest you to create a Calendar table by dax.

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "DateKey",
        YEAR ( [Date] ) * 10000
            + MONTH ( [Date] ) * 100
            + DAY ( [Date] )
)

Measure:

Count Active Customer = 
CALCULATE (
    COUNT ( 'Table'[CustomerKey] ),
    FILTER (
        'Table',
        'Table'[Created Date] <= MAX ( 'Calendar'[Date] )
            && 'Table'[Closed Date] > MAX ( 'Calendar'[Date] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AGuldbrandt]

Thank you for the response and the advice on simplifying the fact. This solution is really close to what I am looking for. The only issue is, when you select another calendar context than date, like month or year, the data model will evaluate your result at the end of the period. So if I chose to look at january, I will have no active customer cases, because the 31-01-2023 isn't within any of my date intervals. Is there any way of evaluating all dates within a calendar context and evaluate them based on the intervals in the fact table. Meaning that a customerkey will get included if one or more datekeys are within the interval? From a business perspective you wanna know which customers have had an active case in january as an example.