Forum Discussion
Help with Running Totals...with some twists
Hi DK_16 ,
We can create four measures to meet your requirement after create two what-if parameter:
M Cool Temp High Faults = IF(SUM('Table'[Colling Temperature]) < [Yerrow Value], 0,IF(SUM('Table'[Colling Temperature]) <[Red Value],1,3))M Cool Temp High SO = SUMX(TOPN(3,FILTER(ALLSELECTED('Table'),'Table'[Substation SN] <= MIN('Table'[Substation SN])),[Substation SN],DESC),[M Cool Temp High Faults])RedNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 6))YellowNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 2 && [M] <6))If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,v-lid-msft , I believe I figured out where our difference comes from. I have a substation sn column that represents each system (Sub_317, Sub 329, etc). Each system has it's own index column based on the event timestamp for each data point (the 6772-6788 values). It looks like you may have been using the index as the substation sn value. I created a unique id column from merging the substation sn and index columns. I used that value in the SO measure and everything seems to work as I would like. Thank you again for your help!
M Cool Temp High SO = SUMX( TOPN( 3, FILTER( ALLSELECTED(SampleData), SampleData[SN_Index] <= MIN(SampleData[SN_Index])), [SN_Index],DESC), [M Cool Temp High Faults])
v-lid-msft , I believe I figured out where our difference comes from. I have a substation sn column that represents each system (Sub_317, Sub 329, etc). Each system has it's own index column based on the event timestamp for each data point (the 6772-6788 values). It looks like you may have been using the index as the substation sn value. I created a unique id column from merging the substation sn and index columns. I used that value in the SO measure and everything seems to work as I would like. Thank you again for your help!
M Cool Temp High SO =
SUMX(
TOPN( 3,
FILTER( ALLSELECTED(SampleData),
SampleData[SN_Index] <= MIN(SampleData[SN_Index])),
[SN_Index],DESC),
[M Cool Temp High Faults])
v-lid-msft, the measures work; however, when I apply them to my full data set (~900k rows), it shuts down for not enough memory. Is there a more memory friendly alternative or am I out of luck because of the nature of the computation?
v-lid-msftCommunity Support
Hi DK_16 ,
If the SN_Index column always increase by 1, we can try to use the following measure to meet your requirement:
M Cool Temp High SO = VAR i = MIN ( SampleData[SN_Index] ) RETURN CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i ) ) + CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i - 1 ) ) + CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i - 2 ) )
Best regards,v-lid-msft , again thank you for your help. Its much appreciated.
The new measure you posted works well, especially in terms of performance, but has one issue.
Columns 1-5 in the screenshot below are what is expected. The issue is that for each of the different Substation SN's (which are the different systems/machines that the data comes from) it is using the index number for all Sub SN's and returning the sum of those. Column 3 is accurate for Sub317, but for Sub281 column 6 should be all 0's based on the 0's in column 4. Instead, it has the same values from Sub317. The same occurs for Sub293 and 308.- v-lid-msft
Hi DK_16 ,
We try to consider the Subtation SN column into code, please try to use the following code:
M Cool Temp High SO = VAR i = MIN ( SampleData[SN_Index] ) VAR SN = SELECTEDVALUE ( SampleData[Subtation SN] ) RETURN CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i && SampleData[Subtation SN] = SN ) ) + CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i - 1 && SampleData[Subtation SN] = SN ) ) + CALCULATE ( [M Cool Temp High Faults], FILTER ( ALLSELECTED ( SampleData ), SampleData[SN_Index] = i - 2 && SampleData[Subtation SN] = SN ) )
Best regards,