topic DAX measure to replace large calculated table in DAX Commands and Tips

DAX measure to replace large calculated table

BBConsultancy — Fri, 31 Jan 2025 14:25:02 GMT

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.

Re: DAX measure to replace large calculated table

bhanu_gautam — Fri, 31 Jan 2025 14:27:16 GMT

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

Re: DAX measure to replace large calculated table

BBConsultancy — Fri, 31 Jan 2025 14:43:45 GMT

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.

Re: DAX measure to replace large calculated table

Poojara_D12 — Sat, 01 Feb 2025 04:55:40 GMT

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 )

Re: DAX measure to replace large calculated table

BBConsultancy — Mon, 03 Feb 2025 08:49:40 GMT

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.