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 pairings between Column 1 and Column 2 (simliar to below):

 

Any help appreciated!

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

6 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

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

      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's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        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]))
  • mauriciosotero's avatar
    mauriciosotero
    Resolver III
    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.