cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mithrandir Regular Visitor
Regular Visitor

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

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.

KeyEventEvent Count
Atrue1
Atrue2
Afalse0
Bfalse0
Btrue1


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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

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

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)

is there a way to.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft Super Contributor
Super Contributor

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

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)

is there a way to.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

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

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

 

Untitled.png

mithrandir Regular Visitor
Regular Visitor

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

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

mithrandir Regular Visitor
Regular Visitor

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

@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.

Super User
Super User

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

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 105 members 1,087 guests
Please welcome our newest community members: