Counting number combinations in two columns

[Freefly]

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

[v-jiascu-msft]

Hi @Freefly,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[augustindelaf]

@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

[Zubair_Muhammad · Accepted solution]

@Freefly

You can also us this MEASURE

Measure =
CALCULATE (
    COUNTROWS ( TableName ),
    ALLEXCEPT ( TableName, TableName[Data1], TableName[Data2] )
)

[Sachy123]

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}})

[Freefly]

Thanks for a quick anser.
Is that the exact syntax? tried to put it in under In in the advace editor but got error

Like 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

[Sachy123]

Perhaps you can use Group BY in the Transform tab