Measurement Time out

[WaelTalaat79]

Dears,

the below highlited calcoulation takes long time to proceed the result in Power BI disktop around 8-10 min

but when i publish the pbix to the Pro user on servier

it show me the below screen shot

So my Question is
1- how to speed up the calcoulation proccess of measurement?
2- How to solve the time out issue in server?

[SpartaBI · Accepted solution]

@WaelTalaat79 change your measure to this:

YTD Prog. Rebates Value =
VAR _Yearly_Purchases_Value = [Yearly Purchases Value]
VAR _slab = [Slab-#]
VAR _result = 
SWITCH(
    _slab,
    0, 0,
    1, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 1] ),
    2, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 2] ),
    3, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 3] ),
    4, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 4] ),
    5, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 5] ),
    6, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 6] )
) * _Yearly_Purchases_Value

RETURN
	_result

Showcase Report – Contoso By SpartaBI

      

[SpartaBI]

@WaelTalaat79  you can't do anything regarding 2 (maybe if you upgrade to PPU but not sure it will give you more resources). You basically need to work on 1 🙂 A measure should take no more than 1/2 seconds, definitely not 8-10 minutes.
Share the code you wrote.

[WaelTalaat79]

the measure below deband on column data from direct source Excel "Slab" 
and the Yearly purchases is a column calcoulation to get the purchases from another table source

step is to add a Share key to lookup the purchase table into slab table

the geting data column for 12 month to calcoulate the Yearly Purchases:

1- MTD Jan. Pur = CALCULATE(SUM('1- Items purchases Data'[Invoice Amt Without Tax]),FILTER(('1- Items purchases Data'),'1- Items purchases Data'[Key]='2022 P-Rebates Slab Condition %'[Key]),FILTER('1- Items purchases Data','1- Items purchases Data'[Date 1].[Year]=2022),FILTER('1- Items purchases Data','1- Items purchases Data'[Date 1].[MonthNo]=1))

"Column " 12- YTD Dec. Pur = [1- MTD Jan. Pur]+[2- MTD Feb. Pur]+[3- MTD Mar. Pur]+[4- MTD apr. Pur]+[5- MTD May Pur]+[6- MTD Jun. Pur]+[7- MTD Jul. Pur]+[8- MTD Aug. Pur]+[9- MTD Sep. Pur]+[10- MTD Oct. Pur]+[11- MTD Nov. Pur]+[12- MTD Dec. Pur]

and then

 measure 

YTD Prog. Rebates Value = if([Slab-#]=0,0,if([Slab-#]=1,(SUM('2022 P-Rebates Slab Condition %'[Slab % 1])*([Yearly Purchases Value])),if([Slab-#]=2,(SUM('2022 P-Rebates Slab Condition %'[Slab % 2])*([Yearly Purchases Value])),if([Slab-#]=3,(SUM('2022 P-Rebates Slab Condition %'[Slab % 3])*([Yearly Purchases Value])),if([Slab-#]=4,(SUM('2022 P-Rebates Slab Condition %'[Slab % 4])*([Yearly Purchases Value])),if([Slab-#]=5,(SUM('2022 P-Rebates Slab Condition %'[Slab % 5])*([Yearly Purchases Value])),if([Slab-#]=6,(SUM('2022 P-Rebates Slab Condition %'[Slab % 6])*([Yearly Purchases Value])))))))))

[SpartaBI · Accepted solution]

@WaelTalaat79 change your measure to this:

YTD Prog. Rebates Value =
VAR _Yearly_Purchases_Value = [Yearly Purchases Value]
VAR _slab = [Slab-#]
VAR _result = 
SWITCH(
    _slab,
    0, 0,
    1, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 1] ),
    2, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 2] ),
    3, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 3] ),
    4, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 4] ),
    5, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 5] ),
    6, SUM ( '2022 P-Rebates Slab Condition %'[Slab % 6] )
) * _Yearly_Purchases_Value

RETURN
	_result

Showcase Report – Contoso By SpartaBI

      

[WaelTalaat79 · Accepted solution]

My Savior 😍
it just 5 seound "OMG , love you man"

ok that is encourge me to ask for mor 2 measure  related
i need to get the monthly impacy by sbtruct the current month value - previous month
but when i do it the calcoulation consider the previous month od Jan is DEC. with value but dec. should be zero

PR for PreivMonth =

(CALCULATE([YTD PR Calculate],PREVIOUSMONTH('Date'[Date].[Date])))

[SpartaBI]

@WaelTalaat79 my pleasure my friend 🙂
For community visabilty better to open a new thread per question, so open a new one and I'll try to get to it. Need to leave PC for now

And hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂