slow rendering

2 Topics

Calculate Statement - Slow Table Load with many informations
Hi guys, I have created this measure (Measure1) that actually obtain the result I want to discover and gives not many problem in limited filter context like table with few rows. Measure1= CALCULATE( [Measure0], FILTER( 'Accounting', 'Accounting'[Fiscal Period] >= RELATED( 'Price Lists'[Fiscal Period] ) )) The problem comes when I add more information in the table report like customer, item, country etc. lead to a very slow loading. The expression within calculate is made by the following measure: Measure0 = SUMX ( FILTER ( 'Price Lists', 'Price Lists'[Old Price]> 0 ), IF ( [ASP] > [Old Price], ( [ASP] - [Old Price] ) * [Quantity], BLANK() )) From the Measure 0 I am creating a context where from the price list I am creating a table with only the values "old price" area greater than zero and then creating and then the if statement. The measure 1 is using the measure 0 only where is retrieving the fiscal period from the price list with related. Can someone help me maybe with a best practice to fix the formula? Really thank you in advance for your help, Regards, ES
Solved
emarome94
3 years ago Place DAX Commands and Tips
1.7KViews
0likes
5Comments
Can this measure be optimized in speed?
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?
Bokazoit
3 years ago Place DAX Commands and Tips
652Views
0likes
3Comments