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)
| DeviceID | Seq | TotalTime | Time | DP | PAVG_Operation | Energy | Energy Cumulative |
| 10042de | 1 | 0 | 0 | 236.833 | 236.833 | 0 | 0 |
| 10042de | 2 | 0.5 | 0.5 | 219.95 | 219.95 | 0.2597 | 0.2597 |
| 10042de | 3 | 0.8333 | 0.3333 | 213.933 | 213.933 | 0.1684 | 0.428 |
| 10042de | 4 | 1.3333 | 0.5 | 228.25 | 228.25 | 0.2695 | 0.6975 |
| 10042de | 5 | 1.6667 | 0.3333 | 224.4 | 224.4 | 0.1766 | 0.8741 |
| 10042de | 6 | 2.1667 | 0.5 | 237.817 | 237.817 | 0.2808 | 1.1548 |
| 10042de | 7 | 2.3333 | 0.1667 | 230.333 | 230.333 | 0.0907 | 1.2455 |
| 10042de | 8 | 2.6667 | 0.3333 | 256.783 | 256.783 | 0.2021 | 1.4476 |
| 10042de | 9 | 2.8333 | 0.1667 | 245.317 | 245.317 | 0.0966 | 1.5441 |
| 10042de | 10 | 3.1667 | 0.3333 | 243.717 | 243.717 | 0.1918 | 1.7359 |
| 10042de | 11 | 3.3333 | 0.1667 | 223.5 | 223.5 | 0.088 | 1.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.
