Forum Discussion
BBConsultancy
Helper I
Helper IDAX measure to replace large calculated table
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...
Poojara_D12
Super User
Super UserYour 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
)
BBConsultancyThank 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.