Dynamic measure based on slicer not calculating totals correctly

[Daveed1973]

I have a measure which is used to dynamically work out the "Filtered Length of Stay" of a Product based on the dates selected in a date slicer. This is the code for the measure...

Filetered Length Of Stay = 
VAR MinDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), FIRSTDATE ( 'Calendar'[Date] ) )
VAR MaxDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), LASTDATE ( 'Calendar'[Date] ) )
RETURN
	-- No filter selected, set LengthOfStay to difference between StartDate and TempEndDate
    IF (ISBLANK ( MinDay ), DATEDIFF (MIN( pbi_Usage12Months[StartedAt] ), MAX( pbi_Usage12Months[TempEndedAt] ),DAY) + 1,

		-- If StartDate is after filtered End Date then set length of stay to 0
		IF (MIN ( pbi_Usage12Months[StartedAt] ) > MaxDay, 0, 

			-- If EndDate before the filtered start date then set LOS to 0
			IF ( MIN ( pbi_Usage12Months[TempEndedAt] ) < MinDay, 0, 
				
				-- If the StartDate is after the filtered start date and EndDate is less than the filtered end date -- Workout difference between StartDate and EndDate
				IF (MIN ( pbi_Usage12Months[StartedAt] ) >= MinDay && MIN ( pbi_Usage12Months[TempEndedAt] ) <= MaxDay, DATEDIFF ( MIN ( pbi_Usage12Months[StartedAt] ), MIN ( pbi_Usage12Months[TempEndedAt] ), DAY ) + 1,
                
					-- If StartDate is after filtered start date and EndDate is after filtered end date then workout difference between StartDate and filtered end date
					IF (MIN ( pbi_Usage12Months[StartedAt] ) >= MinDay && MIN ( pbi_Usage12Months[TempEndedAt] ) >= MaxDay, DATEDIFF ( MIN ( pbi_Usage12Months[StartedAt] ), MaxDay, DAY ) + 1,
                        
						-- StartDate is before filtered start date and EndDate before filtered end date workout difference bewteen filtered start date and EndDate
						IF (MIN ( pbi_Usage12Months[StartedAt] ) <= MinDay && MIN ( pbi_Usage12Months[TempEndedAt] ) <= MaxDay, DATEDIFF ( MinDay, MIN ( pbi_Usage12Months[TempEndedAt] ), DAY ) + 1,
                            
							-- StartDate before filtered start date and EndDate after filtered end date workout difference between filtered start and end date
							IF (MIN ( pbi_Usage12Months[StartedAt] ) <= MinDay && MIN ( pbi_Usage12Months[TempEndedAt] ) >= MaxDay, DATEDIFF ( MinDay, MaxDay, DAY ) + 1
                            )
                        )
                    )
                )
            )
        )
    )

 This works correctly. However the issue I have is when I try and display the results graphically or in a table within Power BI Desktop. The total is always either the highest or the lowest number out of the results. I'm assuming this may be because of the VAR MinDay or the VAR MaxDay showing either the FIRSTDATE or the LASTDATE but I may be wrong. I also do not have any summarise functions available as this is a measure.

As you can see on this screenshot the Filtered Length of Stay is showing 3 which is the lowest value and not a total sum of the column.

Is there a way I can get the Filtered Length Of Stay column to total each row and so that I can perform summarise functions on it?

Thanks in advance.

[v-chuncz-msft]

@Daveed1973,

You may try to create a calculated table instead.

[Daveed1973]

Unfortunately this doesn't seem to work. If I try and create a calculated table based on the same code as above I get an error saying "The expression specified in the query is not a valid table expression". If I simply create a table which is a copy of the Calendar table I only get the Date filed come through and not the "Filtered Length of Stay" measure.

[v-chuncz-msft]

@Daveed1973,

Share us a simplified model and your expected result.

[Daveed1973]

Here is a link to the sample PBIX file.

The table and graph that show the Filtered Length of Stay doesn't total correctly. On the screenshot below the total should be 144 and not 0.

The Full Length of Stay column is simply the number of days between the start date and the end date or the current date + 1. This works our correctly.

[v-chuncz-msft · Accepted solution]

@Daveed1973,

Add a measure as shown below.

Measure =
IF (
    ISFILTERED ( Usage[ProductType] ),
    [Filtered Length Of Stay],
    SUMX ( Usage, [Filtered Length Of Stay] )
)

[Daveed1973]

You Sir are awesome, that has solved the issue. Many thanks for your help!