Need help to optimize slow measures

Bokazoit — Wed, 14 Sep 2022 10:26:42 GMT

VAR MinNumberDonations = MIN('StøttebreveAntal'[Støttebreve antal]) VAR MaxNumberDonations = MAX('StøttebreveAntal'[Støttebreve antal]) VAR MailSum = IF ( SUM(Splittest[DirectMail brev]) >= MinNumberDonations && SUM(Splittest[DirectMail brev]) <= MaxNumberDonations , 1, 0) VAR OtherDonationCount = IF ( MailSum = 1, CALCULATE( COUNT(Splittest[Other Donations]), FILTER(Splittest, [OtherDonation] > 0) ), 0) RETURN IF ( OtherDonationCount >= DonationCountLimit, OtherDonationCount , BLANK())

I am just searching for help to see if the above measure can be optimized with regards to speed.

It works as intended, so its only speed.

MinNumberDonations and MaxNumberDonations is a Parameter set. i.e between 0 and 3

The VAR MailSum calculates if the SUM is within the above mentioned variables. If true, I count the amount of donations, and return the OtherDonationsCount if it's above or equal to the parameter DonationCountLimit - also a fixed integer.

Can it be done so that the measure is faster? I think that I have missed a few best pratices 🙂

This is the result from the analyser:

// DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('StøttebreveAntal'[Støttebreve antal])), 'StøttebreveAntal'[Støttebreve antal] <= 3 ) VAR __DS0FilterTable2 = FILTER( KEEPFILTERS(VALUES('AntalDonationer'[Antal donationer])), 'AntalDonationer'[Antal donationer] = 2 ) VAR __ValueFilterDM1 = FILTER( KEEPFILTERS( SUMMARIZECOLUMNS( 'Splittest'[Donornummer], __DS0FilterTable, __DS0FilterTable2, "SumLotteri_brev", CALCULATE(SUM('Splittest'[Lotteri brev])), "AntalDmBreve", 'Splittest'[AntalDmBreve], "DM_Bidrag", 'Splittest'[DM Bidrag], "Lotteribidrag", 'Splittest'[Lotteribidrag], "AndetBidrag", 'Splittest'[AndetBidrag], "Antal_donorer", 'Splittest'[Antal donorer], "Doner_minus_brev", 'Splittest'[Doner minus brev] ) ), NOT(ISBLANK([Doner_minus_brev])) ) VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL('Splittest'[Donornummer], "IsGrandTotalRowTotal"), __DS0FilterTable, __DS0FilterTable2, __ValueFilterDM1, "SumLotteri_brev", CALCULATE(SUM('Splittest'[Lotteri brev])), "AntalDmBreve", 'Splittest'[AntalDmBreve], "DM_Bidrag", 'Splittest'[DM Bidrag], "Lotteribidrag", 'Splittest'[Lotteribidrag], "AndetBidrag", 'Splittest'[AndetBidrag], "Antal_donorer", 'Splittest'[Antal donorer], "Doner_minus_brev", 'Splittest'[Doner minus brev] ) VAR __DS0PrimaryWindowed = TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Splittest'[Donornummer], 1) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'Splittest'[Donornummer]

Any help is appreciated

Kind regards

David

Re: Need help to optimize slow measures

johnt75 — Wed, 14 Sep 2022 10:53:29 GMT

I have a few suggestions which might speed it up a bit. You are reusing the Splittest[DirectMail brev] sum, you can store it in a variable to make sure it is only calculated once.

You don't need to use FILTER inside CALCULATE, you can pass a filter condition directly into CALCULATE.

I've replaced COUNT with COUNTROWS, I think its more efficient.

Finally, you don't need to return BLANK as the else part of the if clause, that is the default behaviour.

Modified code is

My measure = VAR MinNumberDonations = MIN ( 'StøttebreveAntal'[Støttebreve antal] ) VAR MaxNumberDonations = MAX ( 'StøttebreveAntal'[Støttebreve antal] ) VAR DirectMailBrev = SUM ( Splittest[DirectMail brev] ) VAR MailSum = IF ( DirectMailBrev >= MinNumberDonations && DirectMailBrev <= MaxNumberDonations, 1, 0 ) VAR OtherDonationCount = IF ( MailSum = 1, CALCULATE ( COUNTROWS ( Splittest ), Splittest[OtherDonation] > 0 ), 0 ) RETURN IF ( OtherDonationCount >= DonationCountLimit, OtherDonationCount )

Re: Need help to optimize slow measures

Bokazoit — Thu, 15 Sep 2022 06:33:30 GMT

Thanks that did the trick 🙂

topic Need help to optimize slow measures in DAX Commands and Tips

Need help to optimize slow measures

Re: Need help to optimize slow measures

Re: Need help to optimize slow measures