Count the Unique number of Combinations between Two Columns by Hour

[Anonymous]

Hi!

I'm trying to count the unique number of combinations between two columns by hour.

For example: I have data that looks like this:

I want to get the counts of unique pairings between Column 1 and Column 2:

Any help appreciated!

[ChrisMendoza · Accepted solution]

@Anonymous -

Power Query can take care of that for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXIEYielWB0EzxmFB5EzBLJcgNgVznMDYnc4zwOIPcE8IyDLC4i98fNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hour = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hour", Int64.Type}, {"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Hour"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"Rows"

[Anonymous]

@ChrisMendozathis is helpful! Thank you!

I probably oversimplified my data.

I have more columns than just those two and I worry that counting distinct rows will render the wrong results.
I want it to specifically looks at Column1 and Column2 and ignore everything else.

It seems like count distinct rows would show

0 - 3

1 - 3

2 - 3

Instead of

0 - 2

1 - 3

2 - 1

Additionally, it would be great if I could have this as a measure? So that I am able to drill down to minutes if I would like.

Thanks!

[ChrisMendoza]

@Anonymous -

Just saw that you wanted DAX solution. Power Query Solution below.

Just need to add the equationcriteria, info found @ https://docs.microsoft.com/en-us/powerquery-m/table-distinct.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXIEYicgjlCK1UGIOANxJIoISE0UWMQQyHIBYle4LpCIGxC7w3WBRDyA2BOuywjI8gJib7guZJFIDBGgrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hour = _t, Column1 = _t, Column2 = _t, #"Extra Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hour", Int64.Type}, {"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Hour"}, {{"Count", each Table.RowCount(Table.Distinct(_,{"Column1","Column2"})), type number}})
in
    #"Grouped Rows"

[Anonymous]

@ChrisMendoza 

Thank you so much for you help! This is close.

Ultimately what I'd like to do is to is to have a stacked area chart, showing hours on the x axis and counts on the y axis. I would like to have the legend broken down by unique pairings. So ultimately I would see of the unique pairing count, what is the ratio of each combination.

I think the solution you give loses this granularity. Is there a way to connect this data to the original table? Or how would you recommend doing this?

Hope this makes sense!

[ChrisMendoza]

@Anonymous-

Maybe I don't understand what you're going for. Is this visual not what you're attempting?

This uses the Power Query table.

[Anonymous]

@ChrisMendozaI reposted the quetsion here with some modificaitons: https://community.powerbi.com/t5/Desktop/Unique-Counts-by-Hour/td-p/825212

I'll go ahead and accept your solution on this one!