group by row count

[orihait]

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.

[Anonymous · Accepted solution]

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]

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!!!.

[orihait]

thank you so much!, 

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

[orihait]

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!.