Need help on Performance of DAX formula

dvnnnaidu Awesome! Performance issues in DAX are tough ones!! Make sure you saw my latest post where I think you can shave another 25% off of performance time!!

 

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.

 

 

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.

 

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.

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 I have removed all of the PBIX files. I will share the updated PBIX file with you when I get it cleaned up so that you can review and make sure that it meets your expectations and has all pertinent information removed. Again, really appreciate this I will not publish anything without your consent.

 

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

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

 

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.

 

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