Need help in improving performance of DAX Measure

[latheesh89]

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

[AlB]

@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 

[latheesh89]

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

[Greg_Deckler]

@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

[latheesh89]

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

[Greg_Deckler]

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

[latheesh89]

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

@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

[latheesh89]

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