Is there a way to increment/decrement a column value based on conditions?

[mithrandir]

I have a unique problem i've been trying to solve and haven't found anything similar to apply yet. Essentially, I have a table of events that I want to count and reset based on a condition. In the example below, the "Event Count" column is what I would like to create.

Key	Event	Event Count
A	true	1
A	true	2
A	false	0
B	false	0
B	true	1

You can see that for every Key, I want to increment a count of true Event values, but also reset to 0 every time there is an occurence of false.

It's probably useful to know that each event has a timestamp in my data and I have already sorted and added an index to have the ability to query the data chronologically. The problem I'm having is finding a way in either Power Query or DAX to have a column reference itself. Is there a way to accomplish what I'm trying to do?

[Ashish_Mathur]

Hi,

Write this Query in the Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJCnVVitXBwnFz9AmG8JwweBCFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Event = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Event", type logical}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Key], Text.From([Event], "en-US")}, ""), type text),
    Partition = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Key", "Event", "Index"}, {"Key", "Event", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Merged"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", Int64.Type}, {"Event", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Event]="false" then 0 else [Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns1",{{"Event", Text.Upper, type text}})
in
    #"Uppercased Text"

I am sure there is a much better way that @ImkeF can suggest.

Here is the result i got

[mithrandir]

@Ashish_MathurPutting the values into their own tables is a clever approach, however, if I add to the data a little then you can see that the Table.Group() is combining the logical values in an undesired way.

let
    Source = Table.FromRecords({[Key="A", Event="True"],[Key="A", Event="True"],[Key="A", Event="False"],[Key="A", Event="True"],[Key="A", Event="False"],[Key="B", Event="False"],[Key="B", Event="True"],[Key="B", Event="True"]}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Event", type logical}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Key], Text.From([Event], "en-US")}, ""), type text),
    Partition = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Key", "Event", "Index"}, {"Key", "Event", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Merged"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", Int64.Type}, {"Event", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Event]="false" then 0 else [Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns1",{{"Event", Text.Upper, type text}})
in
    #"Uppercased Text"

I've been playing with your idea of ear-marking the false values, but before the Table.Group() step in order to keep the data separate; no luck yet. Either way, kudos for the reply and a potential solution in M.

[Ashish_Mathur]

Hi,

I understand mine is a rather clunky way to solve the problem.  Hope someone else can suggest a better way to do this in the Query Editor.

[v-jiascu-msft · Accepted solution]

Hi @mithrandir,

Please add the following formula as a calculated column.

Column = var currentIndex = [Index] 
         var lastFalseIndex = CALCULATE(max(Table1[Index]), 
                                        Table1[Event] = FALSE(), 
                                        Table1[Index] <= currentIndex) 
         var minIndexEachKey = CALCULATE(min(Table1[Index]),
                                         all('Table1'[Event], Table1[Index])) 
         return 
             if(ISBLANK(lastFalseIndex),
                 currentIndex - minIndexEachKey + 1 , 
                 currentIndex - lastFalseIndex)

Best Regards,

Dale

[mithrandir]

@v-jiascu-msftThis is a super clean approach in DAX. Thanks for the reply!