Forum Discussion
iHelp with Averagex measure
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 :
Thanks in Advance:)
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))))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)))))
12 Replies
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))))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:)
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)))))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!
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
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,Hi,
Anonymous , amitchandak any suggestions please.
Thanks in Advance
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
)
)
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,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]))
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])
