DAX measure to replace large calculated table

[BBConsultancy]

Hi,

I have a table with contact person history. Each row represents a change in the status of my contact person. For example it shows that person a got status 'Approved' on 01-01-2025, on another row it shows that the same person got status 'Disapproved' at 31-01-2025. I want to visualize the amount of contact persons per status over time, but the issue is that the data only tells me something for 01-01-2025 and 31-01-2025 and not the dates inbetween.

The solution I use for this now is a calculated table, but it returns millions of rows, which impacts the performance. Let me explain the solution:
1. Create calculated column in table

EndDate =

VAR NextDate =

    CALCULATE(

        MIN(ContactpersonHistory[CreatedDate]),

        ALLEXCEPT(ContactpersonHistory, ContactpersonHistory[ContactId]),

        ContactpersonHistory[CreatedDate] > EARLIER(ContactpersonHistory[CreatedDate])

    )

RETURN

    IF(ISBLANK(NextDate), TODAY(), NextDate - 1)
2. Create a calculated table

Contactpersoongeschiedenis per day = GENERATE(Contactpersoongeschiedenis, CALENDAR(Contactpersoongeschiedenis[CreatedDate], Contactpersoongeschiedenis[EndDate]))

The solution works but i'm sure there's a better one that does not impact my performance as much.

[Poojara_D12]

Hi @BBConsultancy 

Your current approach using a calculated table with a daily row for each status is correct conceptually, but it generates a huge number of rows, which can significantly slow down your Power BI model. Instead of materializing all intermediate dates into a calculated table, you can calculate the active status per day dynamically using a measure.

Active Contacts =
VAR SelectedDate = MAX('Calendar'[Date]) -- Get the current date in context
RETURN
CALCULATE(
    DISTINCTCOUNT(ContactpersonHistory[ContactId]), 
    ContactpersonHistory[CreatedDate] <= SelectedDate && 
    ContactpersonHistory[EndDate] >= SelectedDate
)

If you want to split by status, you can extend the measure:

Active Contacts per Status =
VAR SelectedDate = MAX('Calendar'[Date]) 
RETURN
CALCULATE(
    DISTINCTCOUNT(ContactpersonHistory[ContactId]),
    ContactpersonHistory[CreatedDate] <= SelectedDate &&
    ContactpersonHistory[EndDate] >= SelectedDate,
    VALUES(ContactpersonHistory[Status]) -- Ensure status is correctly grouped
)

[BBConsultancy]

Thank you for your reply, but unfortunately it does not work yet. I think it has something to do with the relationships to the date table, since I cannot have two active relations with both CreatedDate and EndDate, to the date table.

[bhanu_gautam]

@BBConsultancy Create a measure to calculate the number of contact persons per status on a given date:

ContactPersonsPerStatus =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
COUNTROWS(ContactpersonHistory),
FILTER(
ContactpersonHistory,
ContactpersonHistory[CreatedDate] <= SelectedDate &&
(ISBLANK(ContactpersonHistory[EndDate]) || ContactpersonHistory[EndDate] >= SelectedDate)
)
)

Create a measure to calculate the number of contact persons per status over time:

DAX
ContactPersonsPerStatusOverTime =
CALCULATE(
[ContactPersonsPerStatus],
DATESBETWEEN(
'Calendar'[Date],
MIN('Calendar'[Date]),
MAX('Calendar'[Date])
)
)

[BBConsultancy]

Thanks for the fast reply. Unfortunately it does not work.

Left is your solution, and right is the expected result. It looks like left isn't accumulating over time.