Filtering out abnormal differences between values

[RalphO]

Hey everyone,

I need to find the minimum value of State of Charge for each day. However, there are some incorrect values in the set in which the state of charge drops to 0 for a second and then returns to normal. This of course changes the daily minimum value to 0. So my question is, how can I remove these incorrect values using a measure?

Note that it is possible for the value to reach zero, just not with that big of a difference in one second of time. 

[v-yulgu-msft · Accepted solution]

Hi @RalphO,

You can try a Measure instead.

abnormal drop =
VAR Timediff =
    SELECTEDVALUE ( Table2[Time] )
        - CALCULATE (
            MAX ( Table2[Time] ),
            FILTER ( ALL ( Table2 ), Table2[Time] < SELECTEDVALUE ( Table2[Time] ) )
        )
VAR previousState =
    CALCULATE (
        SUM ( Table2[State of Charge] ),
        FILTER (
            ALL ( Table2 ),
            Table2[Time]
                = SELECTEDVALUE ( Table2[Time] ) - timediff
        )
    )
RETURN
    IF ( previousState - SELECTEDVALUE ( Table2[State of Charge] ) < 25, "Y", "N" )

Best regards,

Yuliana Gu

[PattemManohar]

@RalphO Just to confirm - You need to have a minimum "State of Charge" for each day but excluding the incorrect values which are flagged as 0 for "State of Charge". So based on sample data, you need to see 5 as "State of Charge" for 2018-07-27 date isn't it ?

[RalphO]

@PattemManohar The value should actually still be 0 in this case, since the last value in the colomn is a correct 0. The drop of the State of Charge here is just 5, while the drop for the first 0 is much higher

[PattemManohar]

@RalphO Oh ! Got you !! Could you please post the same sample data that can be copied.

[RalphO]

@PattemManohar Here you go!

Time	State of Charge
2018-07-27 03:34:59.0000000	67
2018-07-27 03:35:01.0000000	65
2018-07-27 03:35:03.0000000	64
2018-07-27 03:35:05.0000000	63
2018-07-27 03:35:07.0000000	0
2018-07-27 03:35:09.0000000	62
2018-07-27 03:35:11.0000000	61
2018-07-27 03:35:13.0000000	54
2018-07-27 03:35:15.0000000	50
2018-07-27 03:35:17.0000000	32
2018-07-27 03:35:19.0000000	12
2018-07-27 03:35:21.0000000	5
2018-07-27 03:35:23.0000000	0

[PattemManohar]

@RalphO Thanks for that !!

Here is the DAX expression to flag the abnormal difference values. Please add an additional Index Column (Which can be added in Power Query Editor) before adding the below Flag.

Flag = 
VAR _PrevVal = LOOKUPVALUE(Test61AbNormalDiff[StateOfCharge],Test61AbNormalDiff[Index],Test61AbNormalDiff[Index]-1) 
RETURN SWITCH(TRUE(),
              _PrevVal = BLANK(),"Y",
              ABS(Test61AbNormalDiff[StateOfCharge]-_PrevVal)<=25,"Y",
              "N")

Here I've considered any difference more than 25 is an abnormal and flagging that as "N" (You can change this value as per your requirement)

[RalphO]

@PattemManohar That looks good! However, I'm not able to create an index column, since it's a live connection it is not possible. Would there be another way to do that?

[PattemManohar]

@RalphO Ok, then try to create an Index column using RANKX as below

RankIndex = RANKX(Test61AbNormalDiff,Test61AbNormalDiff[Time],Test61AbNormalDiff[Time],ASC) 

Now use this RankIndex field in the above logic to calculate the abnormal difference "Flag"

[RalphO]

@PattemManohar To clarify, I'm not able to create any calculated columns (which I think this solution needs?). I can only create measures. If it try to use the RANKX method I am not able to enter a column in the value portion of the function.

[PattemManohar]

@RalphO Here is another way of doing, Without creating any additional calculated columns

Flag1 = 
VAR _PrevTime = CALCULATE(MAX(Test61AbNormalDiff[Time]),FILTER(ALL(Test61AbNormalDiff),Test61AbNormalDiff[Time]<EARLIER(Test61AbNormalDiff[Time])))
VAR _PrevVal = LOOKUPVALUE(Test61AbNormalDiff[StateOfCharge],Test61AbNormalDiff[Time],_PrevTime)
RETURN SWITCH(TRUE(),
              _PrevVal = BLANK(),"Y",
              ABS(Test61AbNormalDiff[StateOfCharge]-_PrevVal)<=25,"Y",
              "N")

Hope this helps !!

[RalphO]

@PattemManohar unfortunately it still won't work. I can not fill in a column using the EARLIER() for some reason. I assume this is because I am using a streamed dataset, though I'm not sure about that. 

Any other ideas? Thanks for all the help so far!

[PattemManohar]

@RalphO Ok   Here you go..

Flag2 = 
VAR _Time = Test61AbNormalDiff[Time]
VAR _PrevTime = CALCULATE(MAX(Test61AbNormalDiff[Time]),FILTER(ALL(Test61AbNormalDiff),Test61AbNormalDiff[Time]<_Time))
VAR _PrevVal = LOOKUPVALUE(Test61AbNormalDiff[StateOfCharge],Test61AbNormalDiff[Time],_PrevTime)
RETURN SWITCH(TRUE(),
              _PrevVal = BLANK(),"Y",
              ABS(Test61AbNormalDiff[StateOfCharge]-_PrevVal)<=25,"Y",
              "N")

[RalphO]

@PattemManohar That also does not work, because it seems that I can not assign a column to a variable. 

I think this is just a limitation on my part, since you can not figure it out either I'm gonna assume that this limitation is the problem. 

I'll accept one of your replies as the solution (since without the limitation it would be), again thank you for all the help!