How to flag events

[JFAschoon]

Good day All

I have a lot of events that occured(thousands of lines).If it is possible, how do i flag the last event and the first event in a series.The stop event is the end of a series. See example and results below.

Example:                                                           Result:

Start		Start
Foward		forward
Reverse		reverse	1
Stop		stop
Reverse		delay	1
Delay		delay
Power off		power off
Power on		power on
Start		start

Thanks in advance.

[pi_eye]

Hi @JFAschoon, I was able to do it in power query, but with a few preparation steps first.

First you need to:

• Sort by the timestamp so that the records are in order
• Add an index field

now the data will look like this:

The index field is what we will use to tell the next expression where to look for the corresponding values.

Formula for the flag field is:

= Table.AddColumn(#"Added Index", "Flag", each if (List.Last(List.Range(#"Added Index"[Col1],[Index]-1,1)) ="Stop")
     or 
(List.Last(List.Range(#"Added Index"[Col1],[Index]+1,1)) ="Stop")
then 1 else 0)

This is using the List.Range functions to search the 1 cell above and below for the word "Stop" in column [col1]

As there are no cells below or above the first and last records in the table, remember to replace these errors:

= Table.ReplaceErrorValues(#"Added Custom", {{"Flag", 0}})

This will give you:

Further logic, such as limiting to one group can be controlled using the sort step and the if statement logic to whittle down the flag to exactly what is needed

HTH

Pi

[JFAschoon]

Hi @pi_eye .This works good. i can see it in the data. the only problem is that it makes a 9,8 MB file very big. It is currently on 478GB and still counting when closing and applying.

Any suggestions

[pi_eye]

Hi @JFAschoon , that's really strange, adding one column shouldn't increase it that much!

Perhaps it is a bug, do you want to try the suggestion here? https://community.fabric.microsoft.com/t5/Desktop/Pbix-file-size-increases-on-making-a-change-in-the... (closing & re opening)

I wonder if PowerBI is trying to cache, eg all the steps.

In any case, there is a way that you can also achieve this in the front-end DAX. This means that the result will  not take up space in the data model, and will also be dynamic.

I didn't have much time to fiddle about with it, so the formula is less simple than it needs to be! However here is what you can try. 

ReturnVal - is a measure that just returns the current value in [col1]

FlagMeasure then uses the window function OFFSET to return the previous and next values of col1 and checks to see if either of those are "Stop"

returnVal = SELECTEDVALUE(srcTable[Col1])

FlagMeasure = 
var prevVal = CALCULATE([returnVal], 
                OFFSET(-1,,
                        orderby(srcTable[Date of event],asc,
                                srcTable[ID],asc,
                                srcTable[Col1],asc,
                                srcTable[Col2],asc
                                )
                    ))
var nextVal = CALCULATE([returnVal], 
                OFFSET(1,,
                        orderby(srcTable[Date of event],asc,
                                srcTable[ID],asc,
                                srcTable[Col1],asc,
                                srcTable[Col2],asc
                                )
                    ))
return If("Stop" IN { prevVal,nextVal},1,"")

This is the result:

I would recommend hard coding it in the source though, simply because this is dynamic - so you will need to code extra to handle selections and the context in which the measure is applied. The behaiviour of this flag might not be what you are looking for.

Pi

[pi_eye]

Hi @JFAschoon  did this help your case?

[pi_eye]

Hi, can you add more information? The result is missing - additionally, are there time stamps?

[JFAschoon]

Hi,Sorry for that. the column shifted. Under example there is 2 columns the one is example and the one just next to it is the desired result. Yes there is time stamps its seconds and minutes apart from each other