Need help to optimize slow measures

[Bokazoit]

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

[johnt75 · Accepted solution]

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 )

[Bokazoit]

Thanks that did the trick 🙂