Forum Discussion
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 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
2. Create a calculated table
4 Replies
- bhanu_gautam
Super User
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])
)
)
BBConsultancyThanks 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.
- Poojara_D12
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.
