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 n...
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 :)
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!!!.