Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BBConsultancy
Frequent Visitor

DAX 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

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.
4 REPLIES 4
Poojara_D12
Super User
Super User

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
)

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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

BBConsultancy_0-1738334548101.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.