iHelp with Averagex measure

[SUMESHKUMAR22]

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

[amitchandak · Accepted solution]

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

[vapid128]

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

    )

)

[SUMESHKUMAR22]

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

[SUMESHKUMAR22]

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,

[vapid128]

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

[SUMESHKUMAR22]

This is not working. Its giving totally wrong figures.

[v-shex-msft]

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

[SUMESHKUMAR22]

Hi @v-shex-msft ,
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,

[SUMESHKUMAR22]

Hi,
@v-shex-msft , @amitchandak any suggestions please.

Thanks in Advance

[amitchandak · Accepted solution]

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

[SUMESHKUMAR22]

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!

[amitchandak · Accepted solution]

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

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