Forum Discussion

orihait's avatar
orihait
Helper I
7 years ago
Solved

group by row count

hi guys,

i've got a question for you.

i have two relative datasets,

one is data related to projects with the client id as one of the keys,

the other is another dataset with client id and client names.

my goal is to have a visualization using count of projects as the row filter

based on the tables above it should look something like this.

 

as you can see project a and b (and c and d) are owned by the same client so the table above shows

that 2 clients have 2 projects (4 in total). two other clients (4 and 5) have 1 project each (6 in total).

thank you so much in advance :)

ori.

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi orihait 

     

    Not a straight forward one but this is possible.

     

    you need to create a new table with the following DAX

     

    number of projects = GENERATESERIES(1,100,1)
    this will generate a column with 1 to 100 in the cells
     
    Then add a column with
    Number of Clients = COUNTAX(FILTER(projects,projects[client ID]='number of projects'[Value]),projects[client ID] = 'number of projects'[Value])
     
    you will end up with a table with 2 columns, a list of how many clients could be attached to a project and how many time that happens. So:
    1 client attached = 2 projects
    2 clients attached = 2 projects
    3 clients attached = 0 projects
    etc
     
    Then you can create your visual.
     
    If this solves your issue please mark this as the solution :)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi orihait 

     

    Not a straight forward one but this is possible.

     

    you need to create a new table with the following DAX

     

    number of projects = GENERATESERIES(1,100,1)
    this will generate a column with 1 to 100 in the cells
     
    Then add a column with
    Number of Clients = COUNTAX(FILTER(projects,projects[client ID]='number of projects'[Value]),projects[client ID] = 'number of projects'[Value])
     
    you will end up with a table with 2 columns, a list of how many clients could be attached to a project and how many time that happens. So:
    1 client attached = 2 projects
    2 clients attached = 2 projects
    3 clients attached = 0 projects
    etc
     
    Then you can create your visual.
     
    If this solves your issue please mark this as the solution :)
    • orihait's avatar
      orihait
      Helper I
      7 years ago

      thank you so much!, 

      i will check the solution asap and let you know if it works :)

      • orihait's avatar
        orihait
        Helper I
        7 years ago

        hi and again thank you so much for trying to help.

        i should have been clearer about client id's format,

        i chose the number to simplify the example while client id is the company's register id and thus a text field.

        while trying to use the solution you provided i got an error because client id cannout be compared with the integer generated by the GENERATESERIES function.

        it should count how many occurrences of clients with 2 projects exist (number of projects is defined by how many rows have the same client id), in the example in the original post there are two clients that match this definition.

        thanks again!.

         

         
    • orihait's avatar
      orihait
      Helper I
      7 years ago

      hi!,

      after thinking about it a little bit more i attempted the solution you provided from a different angle,

      first i created a calculated collumn counting how many occurrences of each company register exists in the projects table. then i used the new collumn as the one to compare with the generated table.

      thank you so much!!!.