Forum Discussion

phoisan's avatar
phoisan
Icon for Helper I rankHelper I
6 years ago
Solved

Performance issue when calculate Slope and Intercept

Hi,

 

I have some issues with the performance, I create a report to calculate Slope and Intercept value but very slow to get the value.

I'm looking for some recomendations tips or tricks to solve the performance issue.

 

DeviceID, Seq, TotalTime, Time and DP are source data.

 

Airflow and Fan Efficiency are "What-If" parameters.

 
Energy is a measure = (SELECTEDVALUE(Test[DP]) * 'Airflow (m³/h)'[Airflow (m³/s)] * SELECTEDVALUE(Test[Time]))/(10 * 'Fan Efficiency (%)'[Fan Efficiency (%) Value] ).
 
Energy Cumulative is a measure =
VAR __Seq = MAX('Test'[Seq])
VAR _DeviceID =SELECTEDVALUE(Test[DeviceID])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Test'),[Seq]<=__Seq && Test[DeviceID]=_DeviceID),
[Seq],
"Energy",[Energy]
)
Return sumx(__Table,[Energy])
 
Slope Constants =
VAR __Seq = MAX('Test'[Seq])
VAR _DeviceID =SELECTEDVALUE(Test[DeviceID])
VAR Known =
FILTER (
SUMMARIZE(
FILTER(ALL('Test'),[Seq]<=__Seq && Test[DeviceID]=_DeviceID),
[Seq],
"Known[X]",SELECTEDVALUE(Test[TotalTime]),
"Known[Y]",[Energy Cumulative]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)

VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
RETURN
Slope
 
Intercept Constants =
VAR __Seq = MAX('Test'[Seq])
VAR _DeviceID =SELECTEDVALUE(Test[DeviceID])
VAR Known =
FILTER (
SUMMARIZE(
FILTER(ALL('Test'),[Seq]<=__Seq && Test[DeviceID]=_DeviceID),
[Seq],
"Known[X]",SELECTEDVALUE(Test[TotalTime]),
"Known[Y]",[Energy Cumulative]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)

VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =[Slope Constants]
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
Intercept
 
The report take long time to display Energy Cumulative, Slope and Intercept value.

 

Below is the sample data ( actual total rows is 1083)

DeviceIDSeqTotalTimeTimeDPPAVG_OperationEnergyEnergy Cumulative
10042de100236.833236.83300
10042de20.50.5219.95219.950.25970.2597
10042de30.83330.3333213.933213.9330.16840.428
10042de41.33330.5228.25228.250.26950.6975
10042de51.66670.3333224.4224.40.17660.8741
10042de62.16670.5237.817237.8170.28081.1548
10042de72.33330.1667230.333230.3330.09071.2455
10042de82.66670.3333256.783256.7830.20211.4476
10042de92.83330.1667245.317245.3170.09661.5441
10042de103.16670.3333243.717243.7170.19181.7359
10042de113.33330.1667223.5223.50.0881.8239

 

Here is the pbix file

https://www.dropbox.com/s/27om27u8acnwovk/SamplePowerBI.pbix?dl=0

and Source data file

https://www.dropbox.com/s/i9kd76dhs9gr87p/SampleData.xlsx?dl=0

 

Thanks in advance.

Need Help
Tips and Tricks

2 Replies