Can this measure be optimized in speed?

[Bokazoit]

I have this measure that is used to rank customers. In the data model it is 4 different measure, and the final ranking / priority measure is this:

Prio = 
IF ( [RFV DM] > 1, 1, 
    IF ( [Historisk god hitrate] > 0 && [RFV DM] <= 1, 2,
        IF ( [Lotteri spillere] > 0, 3,
            IF ( [Doner minus brev] > 0, 4,0))))

Where

RFV DM = 
    
VAR RFV = CALCULATE(
            ([Direct mail Bidrag])/([Antal Direct Mails]*Brevpris[Værdien Brevpris])
            )

RETURN

IF( [Antal Direct Mails] > 0, RFV, BLANK())

Historisk god hitrate = 

VAR MinAntalBidrag = MIN( 'StøttebrevePrio2'[Antal støttebreve prioritet 2] )
VAR MaksAntalBidrag = MAX( 'StøttebrevePrio2'[Antal støttebreve prioritet 2] )
VAR MinAntalLotteri = MIN( LotteribrevePrio2[Antal lotteribreve prioritet 2] )
VAR MaksAntalLotteri = MAX( LotteribrevePrio2[Antal lotteribreve prioritet 2] )

VAR MailSum = IF ( [Antal Direct Mails] >= MinAntalBidrag && [Antal Direct Mails] <= MaksAntalBidrag, 1, 0)

VAR LotteriSum = IF ( [Antal Lotteri breve] >= MinAntalLotteri && [Antal Lotteri breve] <= MaksAntalLotteri, 1, 0)

VAR DirectCnt = IF ( MailSum = 1 && LotteriSum = 1,
                        CALCULATE(
                            COUNTROWS( Splittest ), Splittest[DirectMail bidrag] > 0 ),
                        0 )

RETURN
IF ( DirectCnt >= AntalDonationerPrio2[Antal donationer prio2], DirectCnt)

Lotteri spillere = 

VAR MinAntalBidrag = MIN( 'StøttebrevePrio3'[Antal støttebreve prioritet 3] )
VAR MaksAntalBidrag = MAX( 'StøttebrevePrio3'[Antal støttebreve prioritet 3] )
VAR MinAntalLotteri = MIN( LotteribrevePrio3[Antal lotteribreve prioritet 3] )
VAR MaksAntalLotteri = MAX( LotteribrevePrio3[Antal lotteribreve prioritet 3] )

VAR MailSum = IF ( [Antal Direct Mails] >= MinAntalBidrag && [Antal Direct Mails] <= MaksAntalBidrag, 1, 0)

VAR LotteriSum = IF ( [Antal Lotteri breve] >= MinAntalLotteri && [Antal Lotteri breve] <= MaksAntalLotteri, 1, 0)

VAR LotteriCnt = IF ( MailSum = 1 && LotteriSum = 1,
                        CALCULATE(
                            COUNTROWS( Splittest ), Splittest[Lotteri bidrag] > 0 ),
                        0 )

RETURN
IF ( LotteriCnt >= AntalDonationerPrio3[Antal donationer prio3], LotteriCnt)

Doner minus brev = 

VAR MinAntalBidrag = MIN ( 'StøttebrevePrio4'[Antal støttebreve prioritet 4] )
VAR MaksAntalBidrag = MAX ( 'StøttebrevePrio4'[Antal støttebreve prioritet 4] )

VAR MailSum =
    IF (
        [Antal Direct Mails] >= MinAntalBidrag
            && [Antal Direct Mails] <= MaksAntalBidrag,
        1,
        0
    )
VAR AndetCnt =
    IF (
        MailSum = 1,
        CALCULATE ( 
            COUNTROWS ( Splittest ), Splittest[Andet bidrag] > 0
             ),
        0
    )

RETURN
    IF ( AndetCnt >= [Antal donationer prio4], AndetCnt )

Each measure is ranking the customers under different conditions. That is why I have different variables in each measure.

The measure prio makes sure that it ranks in correct order.

all measures look through about 4 million plus rows, so it takes like 16-20 sec before the report returns data if a filter is changed. Also each measure takes about 4 seconds each if I look in the optimizer.

Is it possible to optimize the speed though changing the DAX?

[AilleryO]

Hi,

Another suggestion would be to install DAX Studio (if it is not already the case) and try to understand which part of the calculation is the slowest/heaviest.

The DAX studio tool can give you detailed metrics on how your calculation is made, how many lines are retrieved...

So it might help you find which might be optimised.

Hope it might help you find out

[daXtreme]

Hi @Bokazoit 

Nobody will answer your question because to be able to do that one has to have data in front of them and play around with the model. Whether DAX is fast or not depends on many different factors. One of them is what the distribution of the data is and how much data there is (for small models it does not matter whether DAX is written well or badly). Since there's no data here... there can't be any (definitive) answer.

One general advice I can give you is that most often than not the problem of slow DAX stems from the fact that the model is simply wrong or suboptimal. Changing the model often yields simple and fast DAX. If the model is bad, no amount of tweaking will make DAX fast. Simple as that.

[Bokazoit]

I was only looking for DAX optimizations or if there is a missing filter I am missing. The model is Starschema and is as I said 4 sec pr. measure if I only use one of the measures and that is also for the simple RFV consisting of Sum and divide. But thx for the lesson...