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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
latheesh89
Helper II
Helper II

Need help in improving performance of DAX Measure

Hi Techies,

 

I'm trying to create a Length of Stay measure for patients. Begin date should be the latest/max admission date and End date should be based on the slicer date (Stay date).

Here is my sample patient table data,

PatientIDStay DateAdmission DateDischarge DateIn house daysShort break
10001-01-2020 01-01-2020 1 
10002-01-2020 01-01-202002-01-20200 
10003-01-2020 03-01-2020 1 
10004-01-2020 03-01-2020 1 
10005-01-2020 03-01-2020 1 
10006-01-2020 03-01-2020 01
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
 
10021-12-2020 05-01-2020 1 

 

The above patient table is a fact (Large table) tha has millions of records.

I have a separate date dimension for Stay date and used the same as a slicer in my report. Let's say I select Dec-2020 from Stay date [Month Year] then my End date should be 31-12-2020 and Begin date should be latest admission date, so in our case it is 03-01-2020

 

I can calculate LOS based on In house days only because a patient can go on short breaks and return anytime. So I cannot calcualte LOS based on just datediff.

 

I have written DAX calculation like below,


LOS Days:=
VAR MinDate = CALCULATE(MAX('Patient'[Admission Date]), ALL('Patient'[Stay Date]))
VAR MaxDate= MAX('Stay Dates'[Stay Date])
VAR Result = CALCULATE( SUM([InHouse Day]), FILTER(ALL('Stay Dates'),'Stay Dates'[Stay Date]>= MinDate && 'Stay Dates'[Stay Date]<= MaxDate))
RETURN
Result


This works the way I expect  but the performance is very poor. When I try to slice this measure by any dimension it is taking more than a minute to return the results. 

 

I'm sure this can be rewritten in an optimized way so it runs much faster. 

 

Please help me out.

 

Thanks,

Latheesh

8 REPLIES 8
AlB
Super User
Super User

@latheesh89 

I do not understand why MinDate is calculated with a MAX( ) and on 'Patients'

Difficult to say without the actual file but give these a try:

LOS Days =
VAR MinDate =
    CALCULATE ( MAX ( 'Patient'[Admission Date] ), ALL ( 'Patient'[Stay Date] ) )
VAR MaxDate =
    MAX ( 'Stay Dates'[Stay Date] )
VAR Result =
    CALCULATE (
        SUM ( [InHouse Day] ),
        'Stay Dates'[Stay Date] >= MinDate,
        'Stay Dates'[Stay Date] <= MaxDate,
        ALL ( 'Stay Dates' )
    )
RETURN
    Result

The following two assume 'Stay Date' is a well-formed date table:

LOS Days =
VAR MinDate =
    CALCULATE ( MAX ( 'Patient'[Admission Date] ), ALL ( 'Patient'[Stay Date] ) )
VAR MaxDate =
    MAX ( 'Stay Dates'[Stay Date] )
VAR Result =
    CALCULATE (
        SUM ( [InHouse Day] ),
        DATESBETWEEN ( 'Stay Dates'[Stay Date], MinDate, MaxDate ),
        ALL ( 'Stay Dates' )
    )
RETURN
    Result

 

LOS Days =
VAR MinDate =
    CALCULATE ( MAX ( 'Patient'[Admission Date] ), ALL ( 'Patient'[Stay Date] ) )
VAR MaxDate =
    MAX ( 'Stay Dates'[Stay Date] )
VAR Result =
    CALCULATE (
        SUM ( [InHouse Day] ),
        DATESBETWEEN ( 'Stay Dates'[Stay Date], MinDate, MaxDate )
    )
RETURN
    Result

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

@AlB   I tried the above solution but there is no improvement in the performance

Greg_Deckler
Super User
Super User

@latheesh89 - So, yes you can, you could do it this way but may or may not improve performance:

LOS Days:=
VAR MinDate = CALCULATE(MAX('Patient'[Admission Date]), ALL('Patient'[Stay Date]))
VAR MaxDate= MAX('Stay Dates'[Stay Date])
VAR __Table =  FILTER(ALL('Stay Dates'),'Stay Dates'[Stay Date]<= MaxDate)
VAR Result = SUMX(FILTER(__Table, 'Stay Dates'[Stay Date]>= MinDate),[InHouse Day])
RETURN
Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I tried the above solution but there is no improvement in the performance

Greg_Deckler
Super User
Super User

@latheesh89 - Right, you have a similar problem to the posts that I included. You need to see if there is a way to filter your table down more perhaps incrementally so that you are not doing a full table scan. So perhaps try using ALLSELECTED versus ALL for example. It's very difficult to say for certain because not sure how you are using that measure (in what context) so some of the advice would be contingent on that. But yes, you are correct in your assessment of where the performance issue lies and it is a very similar problem to what I solved in those articles. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I tried ALLSELECTED function but it resulted in wrong output. Can we rewrite in a different way that it filters the Patient table by the Max Admission date 1st and then again filter that resulset by Stay date ?

Greg_Deckler
Super User
Super User

@latheesh89 Any way that you can post the PBIX file? These things are notoriously difficult to troubleshoot and optimize. I'm guessing the performance issue is with your ALL statement in your Result variable. Just a guess. 

Performance Tuning DAX - Part 1 - Microsoft Power BI Community

Performance Tuning DAX - Part 2 - Microsoft Power BI Community


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Thanks for responding!

 

Sorry, the report contains PHI information so I cannot post it here. My report contains Patient information along with LOS measure and Admission date. When I remove Admission date then the report runs faster. Like wise for other dimensions too.

 

I doubt the below highlighted part where I had to scan for mindate in the fact table which is huge.

 

 CALCULATE( SUM([InHouse Day]), FILTER(ALL('Stay Dates'),'Stay Dates'[Stay Date]>= MinDate && 'Stay Dates'[Stay Date]<= MaxDate))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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