topic Re: iHelp with Averagex measure in DAX Commands and Tips

iHelp with Averagex measure

SUMESHKUMAR22 — Mon, 11 Jul 2022 05:44:34 GMT

Hi BI Team,

a) W.r.t to below Measure I'm facing performance issue . I Need to Optimize the DAX. Moving this Measure into a card visual is taking more than a minute. When I'm selecting the single NPI number then its working fast but at the end when it perform averagex on the whole data then the perfromance degrades totally. Only date slider is used in the report.

b) Also, if I want to count where the [Measure_] value is >5 then how should I achieve it?

DAX Code :

Measure_ =

AVERAGEX(SUMMARIZE('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number],"@CHECK",DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)))),[@CHECK])

Thanks in Advance:)

Re: iHelp with Averagex measure

amitchandak — Mon, 11 Jul 2022 06:42:09 GMT

SUMESHKUMAR22 , Try one of the two

Measure_ =
AVERAGEX(addolcumns(SUMMARIZE('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number]),"@CHECK",DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)))),[@CHECK])

Measure_ =
AVERAGEX(values('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number]),DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0))))

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Mon, 11 Jul 2022 07:38:21 GMT

Hi amitchandak ,

Thanks for your prompt response.

The above dax's are not givng the Total average rather its giving the SUM inted of AVG .
In the below screenshot 10.41 & 10.41 is coming according to the above measures. But it should be 5.04 instead. Can you please recheck the code.
Thanks in Advance:)

Re: iHelp with Averagex measure

amitchandak — Mon, 11 Jul 2022 07:41:05 GMT

SUMESHKUMAR22 , Try with one additional calculate

Measure_ =
AVERAGEX(values('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number]),calculate(DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)))))

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Mon, 11 Jul 2022 08:19:47 GMT

Hi amitchandak ,

Your following measure worked but when it comes to performance our mesaures are not upto the mark. It needs optimization.

Measure_in reply= Duration (78238ms)
AVERAGEX(values('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number]),calculate(DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)))))

Measure_I mentioned = Duration (78266ms)
AVERAGEX(SUMMARIZE('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number],"@CHECK",DIVIDE(sum('PP_CTL_SFTP'[impressions]),CALCULATE(COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)))),[@CHECK])

Both the measures are taking similar amount of time. Nothing significantly less.
After date filtering its taking ,
Measure_I mentioned= Duration (90423ms)
Measure_in reply = Duration (90355ms)

Thanks!

Re: iHelp with Averagex measure

Anonymous — Thu, 14 Jul 2022 07:36:25 GMT

HI SUMESHKUMAR22,

Your formula seems well and not existed the nested looping which may affect the performance.

How many rows are your tables stored? Did the 'impressions' means the table column or measure expression? please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Thu, 21 Jul 2022 04:36:49 GMT

Hi Anonymous ,
I have optimized the measure in the below way but when I check the values for the whole date range then it gives me this below error which is because this dax code needs more optimization & it cannot evaluate for the whole date range.
Measure:

AVERAGEX(SUMMARIZE('DA Internal','DA Internal'[NPI_NUMBER],"@CHECK",DIVIDE(DA_CAMPAIGNS[IMPRESSION],CALCULATE(sumx(values('DA Internal'[EVENT_DAY]),1),filter( 'DA Internal',DA_CAMPAIGNS[IMPRESSION] >0)))),[@CHECK])
No. of data rows is approx. 50 million. When I'm adding the above measure the table is throwing the below error but when I remove this measure this error didn't come up.
Impression is the measure expression. Its a simple distinct count with filter condition.
Visual used : Table
Error:

Thanks in Advance,

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Thu, 21 Jul 2022 04:38:08 GMT

Hi,
Anonymous , amitchandak any suggestions please.

Thanks in Advance

Re: iHelp with Averagex measure

vapid128 — Thu, 21 Jul 2022 04:59:39 GMT

add table:

SUMMARIZE(

'PP_CTL_SFTP',

'PP_CTL_SFTP'[NPI Number],

"@CHECK",

DIVIDE(

sum('PP_CTL_SFTP'[impressions]),

CALCULATE(

COUNTROWS(DISTINCT('PP_CTL_SFTP'[Day])),

filter( 'PP_CTL_SFTP','PP_CTL_SFTP'[impressions] >0)

)

then average @check

Or you can try this

add table:

newTable = values('PP_CTL_SFTP'[NPI Number])

then link new table to old one with many to one relationship

add colnum:

CHECK>0 =

DIVIDE(

CALCULATE(sum('PP_CTL_SFTP'[impressions])),

CALCULATE(

DISTINCTCOUNT('PP_CTL_SFTP'[Day]),

'PP_CTL_SFTP'[impressions] >0

)

CHECK>5 =

DIVIDE(

CALCULATE(sum('PP_CTL_SFTP'[impressions])),

CALCULATE(

DISTINCTCOUNT('PP_CTL_SFTP'[Day]),

'PP_CTL_SFTP'[impressions] >5

)

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Thu, 21 Jul 2022 05:16:51 GMT

Hi vapid128 ,

Appreciate your response & effort but I want to build this logic using a measure itself because I have a date & category filter in the report. So if I use both in the summarize table it will affect the numbers which are grouped according to NPI Number. Right?

Thanks in Advance,

Re: iHelp with Averagex measure

vapid128 — Thu, 21 Jul 2022 05:37:12 GMT

When you are getting performance issue, it is time to stop using complex measures.

That is actually a really big topic. It is gonna be very different for cases.

Let's try this way:

add colnum at PP_CTL_SFTP table.

CHECK>0 =

CALCULATE(

DIVIDE(

sum('PP_CTL_SFTP'[impressions]),

CALCULATE(

DISTINCTCOUNT('PP_CTL_SFTP'[Day]),

'PP_CTL_SFTP'[impressions] >0

)

ALLEXCEPT('PP_CTL_SFTP','PP_CTL_SFTP'[NPI Number])

add measure

measure =

AVERAGEX(Values('PP_CTL_SFTP'[NPI Number]),CALCULATE(max[CHECK>0]))

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Thu, 21 Jul 2022 11:30:37 GMT

This is not working. Its giving totally wrong figures.

Re: iHelp with Averagex measure

SUMESHKUMAR22 — Thu, 21 Jul 2022 11:35:43 GMT

The code which you mentioned needs optimization at the best level & I have already tried this long back by splitting up my measure part.
I have 50 million records which means if i run this table logic or my current measure without any date filter it will show memory conflict error.
Current Measure (which is giving value that I expected):
AVERAGEX(SUMMARIZE('DA Internal','DA Internal'[NPI_NUMBER],"@CHECK",DIVIDE(DA_CAMPAIGNS[IMPRESSION],CALCULATE(sumx(values('DA Internal'[EVENT_DAY]),1),filter( 'DA Internal',DA_CAMPAIGNS[IMPRESSION] >0)))),[@CHECK])