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

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,

 PatientID Stay Date Admission Date Discharge Date In house days Short break 100 01-01-2020 01-01-2020 1 100 02-01-2020 01-01-2020 02-01-2020 0 100 03-01-2020 03-01-2020 1 100 04-01-2020 03-01-2020 1 100 05-01-2020 03-01-2020 1 100 06-01-2020 03-01-2020 0 1 ... ... ... ... ... 100 21-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
Super User

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

Helper II

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

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

``````

@ 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...
Helper II

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

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.

@ 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...
Helper II

@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 ?

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

@ 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...
Helper II

@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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors