Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to count consecutive values with a filter as per below example.
CustomerID OrderNumber Registered Result
1 A Yes 1
2 B No 0
3 C Yes 1
1 D Yes 2
2 E No 0
1 F Yes 3
3 G Yes 2
Thanks in advance
Solved! Go to Solution.
Hi @chriser
Try this for a calculated column:
Result = IF ( Table1[Registered] = "Yes", CALCULATE ( COUNT ( Table1[CustomerID] ), ALLEXCEPT ( Table1, Table1[CustomerID] ), Table1[OrderNumber] <= EARLIER ( Table1[OrderNumber] ) ), 0 )
@chriser -
Power Query as a possible solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCYj98sFcYyDTGUkapNwFTbkrQjlI2g1JGqTdHcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CustomerID = _t, OrderNumber = _t, Registered = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"OrderNumber", type text}, {"Registered", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"Count", each _, type table [CustomerID=number, OrderNumber=text, Registered=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Trans", 1,1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OrderNumber", "Registered", "Trans"}, {"OrderNumber", "Registered", "Trans"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Result", each if [Registered] = "Yes" then [Trans] else 0), #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"OrderNumber", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Count", "Trans"}) in #"Removed Columns"
Proud to be a Super User!
Hi @chriser
Try this for a calculated column:
Result = IF ( Table1[Registered] = "Yes", CALCULATE ( COUNT ( Table1[CustomerID] ), ALLEXCEPT ( Table1, Table1[CustomerID] ), Table1[OrderNumber] <= EARLIER ( Table1[OrderNumber] ) ), 0 )
Thanks @AlB! Works really well, although this approach is memory hungry. My poor Surface failed to deliver results on over 1m rows, but after filtering table to 8k rows it worked well. Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |