Forum Discussion
Helper IIICOUNTIF or AGGREGATE Merge in Power Query
Hi,
I have two tables (Snippet Below):
Sales and Bookings 2019:
Unique Key:
4333-Austria-VAT Return
7384-Germany-VAT Return
WorkflowData
Unique Key:
4333-Austria-VAT Return
4333-Austria-VAT Return
7384-Germany-VAT Return
4333-Austria-VAT Return
4333-Austria-VAT Return
4333-Austria-VAT Return
7384-Germany-VAT Return
...
My goal is to create a new column on the Sales and Bookings 2019 table to count the number of times that unique key is matched in the Workflow Data. I tried using an aggregate merge I eventually cancelled this applied step as it was taking too long. The number of rows on the Workflow table is 150,000+
i.e.
Sales and Bookings 2019:
Unique Key: ReturnCount
4333-Austria-VAT Return 5
7384-Germany-VAT Return 2
If you absolutely, for sure, no question need this as a calculated DAX column, there are more efficient ways than using MAXX and COUNTX.
ReturnCount = COUNTROWS( FILTER( WorkflowData, WorkflowData[Non-Unique Key] = [Unqiue Key]))
You can also do this in power query, if you don't want to load in all those rows in the first place. The lines in red are where the joining happens.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2NtZ1LC0uKcpMVIrViVYyN7Yw0XVPLcpNzKtUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Key", "Unique Key"}}), #"CountedKeys" = Table.Group(WorkflowData, {"Non-Unique Key"}, {{"ReturnCount", each Table.RowCount(_), type number}}), #"Merge Tables" = Table.Join(#"Renamed Columns", "Unique Key", #"CountedKeys", "Non-Unique Key") in #"Merge Tables"
5 Replies
- amitchandak
Super User
Try
Countx(filter(WorkflowData,'Sales and Bookings 2019'[Unique Key] =WorkflowData[Unique Key]),WorkflowData[Unique Key])
Edited
united2winHi Amit,
Thank you for your reply. Unfortunately, doesn't seem to work. The filter function does not have enough arguments.
Alex
- amitchandak
I think some copy paste error while I replace in my formula
Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) CountX(filter('Other table','Other table'[id]='Self'[ID]),'Other table[Order Id])
- Cmcmahan
Resident Rockstar
Before creating this as a calculated column, I would take a step back and ask why you need to store the info in a table instead of counting it on the fly. PowerBI includes count as one of the default aggregations. I literally just added the data you provided and made sure the tables were related by the Key, and then dragged them into this table visual and set the Workflow Key to count.
- Cmcmahan
If you absolutely, for sure, no question need this as a calculated DAX column, there are more efficient ways than using MAXX and COUNTX.
ReturnCount = COUNTROWS( FILTER( WorkflowData, WorkflowData[Non-Unique Key] = [Unqiue Key]))
You can also do this in power query, if you don't want to load in all those rows in the first place. The lines in red are where the joining happens.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2NtZ1LC0uKcpMVIrViVYyN7Yw0XVPLcpNzKtUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Key", "Unique Key"}}), #"CountedKeys" = Table.Group(WorkflowData, {"Non-Unique Key"}, {{"ReturnCount", each Table.RowCount(_), type number}}), #"Merge Tables" = Table.Join(#"Renamed Columns", "Unique Key", #"CountedKeys", "Non-Unique Key") in #"Merge Tables"
