Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Unique Counts by Hour

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 pairi...
Need Help
  • mauriciosotero's avatar
    mauriciosotero
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
mauriciosotero
Resolver III
6 years ago

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.