Unique Counts 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 (simliar to below):

Any help appreciated!

[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 (simliar to below):

Any help appreciated!

[mauriciosotero · Accepted solution]

First, check if the default summarization of the column hour is “Don’t summarize”:

Then, create a new column for the Unique combination you want to count:

Unique Combination = CONCATENATE('Table'[Column1],'Table'[Column2])

So, create a measure Counts:

Counts = COUNT('Table'[Hour])

Finnaly, you can create you a table to visualize your result:

If it helps, pls mark this post as a solution and give a kudo. Thanks

[Anonymous]

@mauriciosoterothis is very helpful!

There's one thing that I don't think is quite right.
I think the measure isn't working quite right?

For example, when I plot:
Axis: HOUR
Values: Counts

When I plot:
Axis: HOUR

Values: Counts

Legend: Unique Combinations

I think it is related to this: https://community.powerbi.com/t5/Desktop/Stacked-line-chart-doesn-t-stack/td-p/52108

My boss WANTS a stacked area chart, so it would be great if I could get it to stack 🙂

[mauriciosotero]

Hi,

I think the problem is because the zero counts are not calculated, so it is not plotted. I made a calculated table, so the zeros could appear. It is what do you need? A plotted an area chart, I think it is better.

You can donwload the file in this link: Pbix file 

If helps, I appreciate a kudo and mark as the solution.

[Nathaniel_C]

Hi @Anonymous 

= CALCULATE (COUNT(table[Hour],ALLEXCEPT(table,table[Column 1],table[Column 2]))

Then you could use concatenate to create a new column that combined the values.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_CThanks for the response.

I received:

Too many arguments were passed into the COUNT function. The maximum argument count for the function is 1.

[Nathaniel_C]

Hi @Anonymous ,

Sorry dropped a paren after hour
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

= CALCULATE (COUNT(table[Hour]),ALLEXCEPT(table,table[Column 1],table[Column 2]))