Forum Discussion
Counting number combinations in two columns
Hi, trying to count how many time a specif pair fo data occurs in two colums. I dont know what combination that my occur and the point is to find out what pair or combination that is most frequent.
Tabel 1
Data1 Data2
1 2
3 5
1 2
3 4
6 1
1 2
Mesure
Data1 Data2 Count
1 2 3
3 5 1
3 4 1
6 1 1
Thanks
You can also us this MEASURE
Measure = CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Data1], TableName[Data2] ) )
6 Replies
I used Group by in the Edit Queries and grouped the data with data 1 and data 2.
Is this what you want?
= Table.Group(#"Changed Type", {"data 2", "data 1"}, {{"Count", each Table.RowCount(_), type number}})Thanks for a quick anser.
Is that the exact syntax? tried to put it in under In in the advace editor but got errorLike this
https://msdn.microsoft.com/en-us/library/mt260774.aspx#Arguments
PrefeblyI dont what to alter the columns since I used them for other things as well and these are just two of many columns in the DB
Perhaps you can use Group BY in the Transform tab
Freefly I found a way to count that in Power BI Desktop
1. Be sure your columns are tagged as text.
2. You have to concatenate columns Data1 and Data2 (see image):
Use the function "add a Custom Column" and Concatenate your fields :
Now it should look like that :
Then, use the function "Group By" (see image, in red):
Group by "Concatenate",
Operation : "Count"
You now have your "Counting" operation that is automated ! See image
You can also us this MEASURE
Measure = CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Data1], TableName[Data2] ) )
