Need help on Performance of DAX formula

[dvnnnaidu]

Hi,

I am trying to find out No. of orders which stayed with a particular department during the order life cycle.

For this we have data which shows Start Date and End Date of Order with each department. 

I have used the below DAX for this , I am getting the result which is requried , but perfomance of this is terrible, it takes about 30 minutes to load visual which is having this measure.

My date table is with houly Date Time.

Please help improve the performance and suggest  better way to get the results

DAX:

No. of Orders =

VAR StartDate =

VALUE ( SELECTEDVALUE ( 'Tracking History'[t_sdate]) )

VAR EndDate =

VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date]

) )

VAR MinDateInContext =

VALUE ( MIN ( 'DateTimeTable'[DateTime] ) )

VAR MaxDateInContext =

VALUE ( MAX ( DateTimeTable[DateTime] ) )

RETURN

IF (

AND ( StartDate > MinDateInContext, EndDate < MaxDateInContext ),

1,

IF (

and(AND (StartDate > MinDateInContext, EndDate>MaxDateInContext),MaxDateInContext>StartDate),

1,

IF (

and( AND ( StartDate < MinDateInContext, EndDate < MaxDateInContext ),EndDate>MinDateInContext),

1,

IF (

AND ( StartDate < MinDateInContext, EndDate > MaxDateInContext ),

1,

BLANK ()

)

)

)

)

[Greg_Deckler · Accepted solution]

@dvnnnaidu - I have updated the PBIX to return the same results that you had in your original visual. It is on Page 3 of the updated PBIX attached. I have run tests and this visual renders in right around 1 minute and I don't have the worlds greatest laptop. Hopefully your results are similar. If so, that is a reduction in rendering time by 20 or 30 times.

[Greg_Deckler]

OK, sample data would be tremendously helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, for the sanity of everyone involved, I have pasted a cleaned-up version of the code below. 

I do not know why you are using VALUE the way you are. I will try to convert this formula to a SWITCH statement because nested IF's are terrible.

No. of Orders =
	VAR StartDate =
		VALUE ( SELECTEDVALUE ( 'Tracking History'[t_sdate]) )
	VAR EndDate =
		VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date]) )
	VAR MinDateInContext =
		VALUE ( MIN ( 'DateTimeTable'[DateTime] ) )
	VAR MaxDateInContext =
		VALUE ( MAX ( DateTimeTable[DateTime] ) )
RETURN
	IF (
		AND ( 
			StartDate > MinDateInContext, 
			EndDate < MaxDateInContext
		),
		1,
		IF (
			AND(
				AND (
					StartDate > MinDateInContext,
					EndDate > MaxDateInContext
				),
				MaxDateInContext>StartDate
			),
			1,
			IF (
				AND( 
					AND ( 
						StartDate < MinDateInContext, 
						EndDate < MaxDateInContext 
					),
					EndDate>MinDateInContext
				),
				1,
				IF (
					AND ( 
						StartDate < MinDateInContext, 
						EndDate > MaxDateInContext
					),
					1,
					BLANK ()
				)
			)
		)
	)

[Greg_Deckler]

OK, here is cleanup attempt #1:

No. of Orders =
	VAR StartDate = SELECTEDVALUE ( 'Tracking History'[t_sdate])
	VAR EndDate = SELECTEDVALUE ( 'Tracking History'[End Date])
	VAR MinDateInContext = MIN ( 'DateTimeTable'[DateTime] )
	VAR MaxDateInContext = MAX ( DateTimeTable[DateTime] )
RETURN
	SWITCH(TRUE(),
		StartDate > MinDateInContext && EndDate < MaxDateInContext,1,
		MaxDateInContext > StartDate && StartDate > MinDateInContext && EndDate > MaxDateInContext,1,
		StartDate < MinDateInContext && EndDate > MaxDateInContext,1,
		BLANK()
	)

[Greg_Deckler]

So, in looking at this, it is difficult to follow the logic that you wish to apply here. But, in general what you want to do is eliminate as much logic processing as possible. In the SWITCH statement, structure the lines so that as many lines as possible get eliminated during the top-most tests. In other words, you want the most frequent logical test that meets the most test cases to appear at the top of the SWITCH statement.

You might also consider reversing your logic. If you have many rows that will result in a BLANK and only a few rows that result in 1, put the logic tests for finding BLANK rows in your SWITCH statement. Otherwise, most of the rows have to go through all of the tests for 1 before "falling through" and being assigned BLANK versus if you test for BLANK in your SWITCH statement you may be eliminating many, many rows.

Again, it is incredibly difficult to give you specific advice in your situation. There may be entirely different methods of doing this via relationships or unpiving columns or who knows. But we will never get to those solutions without example data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[dvnnnaidu]

Dear @Greg_Deckler ,

Thank you very much for your quick reply, I am trying out on the DAX which you have mentioned but visual is not getting rendered saying relationships are missing, I will try the SWITCH function.

Mean while as you have mentioned in the below link my working file is attached.

In the table "Tracking History" No. of Orders measure is where I need your help and this menasure in turn is used in "Total Orders" measure.

I am using this measure to plot a line chart against departments to see the No. of orders pending at a certain point of time 

https://1drv.ms/u/s!Am0-IA7NI8VAtTBlY1H4CF5Hu3qL 

[Greg_Deckler]

Hmm, I am not able to open that PBIX file, what version of the Desktop are you on? File | Options and Settings | Options | Diagnostics.

[dvnnnaidu]

@Greg_Deckler ,

I am using March 2020 version

2.79.5768.663 64-bit (March 2020)

[Greg_Deckler]

@dvnnnaidu - Yep, I just downloaded it. It wasn't updating because I always have it open.

Let's backup a minute. What are you trying to achieve with your visual? Because I see some concerning things in your data model where you have inactive relationships with your DateTime table, etc. What are you trying to achieve with your visual? Are you basically kind of trying to get a sense of things in progress during a date interval? Kind of like Open Tickets here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[Greg_Deckler]

@dvnnnaidu - I made a few changes to the model and check out Page 3 of the attached PBIX below under my signature. I do not know if it is returning the correct results or not. First, I removed both of the inactive relationships between Tracking History and DateTimeTable. Then I wrote this measure:

No. of Orders 2 = 
	VAR StartDate = MAX ( 'Tracking History'[t_sdate])
	VAR EndDate = MAX ( 'Tracking History'[End Date])
	VAR MinDateInContext = MIN ( 'DateTimeTable'[DateTime] )
	VAR MaxDateInContext = MAX ( DateTimeTable[DateTime] )
RETURN
	SWITCH(TRUE(),
		StartDate > MinDateInContext && EndDate < MaxDateInContext,1,
		MaxDateInContext > StartDate && StartDate > MinDateInContext && EndDate > MaxDateInContext,1,
		StartDate < MinDateInContext && EndDate > MaxDateInContext,1,
		BLANK()
	)

I used this measure in a copy of your visual. I put this on Page 3 of the report. It is very fast. It is very slow if you try to use SUMX of this across the Tracking History table because SUMX is an iterator function. Think table scans = bad. But, I do not think you need to do such a SUMX but I may be missing something.

[dvnnnaidu]

@Greg_Deckler ,

Result is returning all 1 which is not actual

[dvnnnaidu]

@Greg_Deckler ,

SUMX is required as the measure either through SWITCH or IF conditions has to happen at a rwo level, other wise when the contaxt is only date and department then result will always be one.

Where as I require the measure to be calculated at every row level and then should get agrregated which will give the right results.

[Greg_Deckler · Accepted solution]

@dvnnnaidu - I have updated the PBIX to return the same results that you had in your original visual. It is on Page 3 of the updated PBIX attached. I have run tests and this visual renders in right around 1 minute and I don't have the worlds greatest laptop. Hopefully your results are similar. If so, that is a reduction in rendering time by 20 or 30 times.

[dvnnnaidu]

@Greg_Deckler ,

Thank you very much for your , Performance has improved drastically with the solution you have given. Thank you

Best Regards,

Narasimha Naidu

@dvnnnaidu 

[Greg_Deckler]

@dvnnnaidu - I don't know why I can't let this go, but I knocked another 25% off of the rendering time, Total Orders 4 on Page 5 renders in about 30 seconds on my machine.

Updated PBIX is attached. Basically, pre-filtered the table that iterations are performed over. Eliminates some rows and improves logic.

[Greg_Deckler]

@dvnnnaidu - Favor to ask, would you mind if I used your file as a basis for a blog article on DAX performance tuning? I think it could help a lot of people out. No worries if not.

[dvnnnaidu]

Hi Greg,

I cannot say no , but please use only two tables from the file Tracking History and Date Table and Departments (If you can change the names of departments would be better) also in the tracking history please remove column t_plni

Also , if possible can you do me favour by removing the files posted on this post.

[Greg_Deckler]

100% will do. Thanks @dvnnnaidu I will take time to get rid of everything I can and change everything so that it no longer has any identifying data. Much appreciated. Will get rid of the files now. 

[dvnnnaidu]

Hi Greg,

Can you paste the last updated DAX code as i din't get the file link where you said within 30 seconds it gets refreshed, only code if can paste will be great

[Greg_Deckler]

Sure @dvnnnaidu !

Total Orders 4 = 
    VAR MinDateInContext = MIN ( 'DateTimeTable'[DateTime] )
	VAR MaxDateInContext = MAX ( DateTimeTable[DateTime] )
    VAR __Table = 
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                ('Tracking History'[t_sdate] > MinDateInContext || 
                    'Tracking History'[End Date] > MaxDateInContext)
            ),
            "__No of Orders",
                SWITCH(TRUE(),
                    'Tracking History'[t_sdate] > MinDateInContext &&
                        'Tracking History'[End Date] > MaxDateInContext,BLANK(),
                    MaxDateInContext < 'Tracking History'[t_sdate] && 'Tracking History'[t_sdate] > MinDateInContext && 'Tracking History'[End Date] > MaxDateInContext,BLANK(),               
                    1
                )
        )            
RETURN
    SUMX(__Table,[__No of Orders])

[Greg_Deckler]

@dvnnnaidu See the attached Excel file, I have stripped out any and all unnecessary columns. Basically there are some Dates, generic departments and the only thing I left in the Tracking History was 3 columns, the department id, start date, end date. Let me know if it is acceptable. I will create an entirely new PBIX file with only this data in it.

[dvnnnaidu]

Hi Greg,

In the attached excel there is nothing which relates to our data. So, with no objection you can use this

Once again thank you for help