topic Improving Dax Query Performance in DAX Commands and Tips

Improving Dax Query Performance

Anonymous — Tue, 28 Feb 2023 16:13:41 GMT

Hi all,

I am looking to improve query performance. The report measures employee activity in a software, showing how many times someone "posted". I have a few lengthy measures in this report which are probably causing the visual on the right to be slow or even overflow. I have tried to clean it up a bit, but it is still pretty slow according to the Performance Analyzer. I would welcome any ideas here! I have attached the .pbix below.

Thanks!

- 170/243/8 refer to employee codes

https://drive.google.com/file/d/1pQH4V1aSxYVWnQd-QMcmknrwu_ImbQ_5/view?usp=sharing

Re: Improving Dax Query Performance

wdx223_Daniel — Wed, 01 Mar 2023 09:13:57 GMT

it seams more efficient to change the measure of Unproductive Time as this

Unproductive Time2 = VAR _tbl = SUMMARIZE ( 'Audit', Audit[code], Audit[master_key], Audit[trans_date] ) VAR UnproductiveMinutes = SUMX ( _tbl, VAR _c = Audit[trans_date] VAR _p = MAXX ( OFFSET ( -1, _tbl, ORDERBY ( Audit[trans_date] ) ), Audit[trans_date] ) VAR _min = ( _c - _p ) * 1440 RETURN IF ( _min >= 10 && FORMAT ( _c, "yyyymmdd" ) = FORMAT ( _p, "yyyymmdd" ), _min ) ) VAR hourNo = INT ( MOD ( UnproductiveMinutes, 1440 ) / 60 ) VAR minuteNO = MOD ( MOD ( UnproductiveMinutes, 1440 ), 60 ) VAR secondNo = INT ( ( UnproductiveMinutes - INT ( UnproductiveMinutes ) ) * 60 ) RETURN IF ( NOT ISBLANK ( UnproductiveMinutes ), FORMAT ( hourNo, "#0" ) & ":" & FORMAT ( minuteNO, "#00" ) & ":" & FORMAT ( secondNo, "#00" ) )

Re: Improving Dax Query Performance

Anonymous — Thu, 02 Mar 2023 20:45:36 GMT

Thank you for this suggestion! It appears to slightly improve performance. Is there any way around a lengthy waiting process just due to the complexity of the measure?

Re: Improving Dax Query Performance

Anonymous — Thu, 02 Mar 2023 20:46:08 GMT

Any ideas here? Would welcome any thoughts!