Forum Discussion
Some help with right filtering needed
You might try the following. I don't know if this is the best solution, but I think this might work.
A little adoptation of Reza Rad's create row number per group.
The first step would be that you follow the instruction on this blog page by Reza Rad:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
The only difference that I would suggest is to let the index number start at 0 (although 1 would work as wel).
After performing this step you get something like
Ticket Number Grade Description Index 12345 A Apple 0 54321 A Orange 0 54321 B Banana 1 54321 C Peach 2 54321 C Pear 3 After this you could do a regular group by through the UI. Choose advanced options to include all the columns. Don't perform a sum, but perform a max on the Index column.
Then you get something like this:
Ticket Number Grade Description Index numberofsubtickets 12345 A Apple 0 0 54321 A Orange 0 3 54321 B Banana 1 3 54321 C Peach 2 3 54321 C Pear 3 3 After this step you could add a custom column. If index = 0 and numberofsubtickets > 0 then 0 else if
index > 0 and numberofsubtickets > 0 then 1 else 1
Ticket Number Grade Description Index numberofsubtickets Number 12345 A Apple 0 0 1 54321 A Orange 0 3 0 54321 B Banana 1 3 1 54321 C Peach 2 3 1 54321 C Pear 3 3 1 After this you can perform a group by and sum on the number column and this will give the result your looking for.
You might try the following. I don't know if this is the best solution, but I think this might work.
A little adoptation of Reza Rad's create row number per group.
The first step would be that you follow the instruction on this blog page by Reza Rad:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
The only difference that I would suggest is to let the index number start at 0 (although 1 would work as wel).
After performing this step you get something like
| Ticket Number | Grade | Description | Index |
| 12345 | A | Apple | 0 |
| 54321 | A | Orange | 0 |
| 54321 | B | Banana | 1 |
| 54321 | C | Peach | 2 |
| 54321 | C | Pear | 3 |
After this you could do a regular group by through the UI. Choose advanced options to include all the columns. Don't perform a sum, but perform a max on the Index column.
Then you get something like this:
| Ticket Number | Grade | Description | Index | numberofsubtickets |
| 12345 | A | Apple | 0 | 0 |
| 54321 | A | Orange | 0 | 3 |
| 54321 | B | Banana | 1 | 3 |
| 54321 | C | Peach | 2 | 3 |
| 54321 | C | Pear | 3 | 3 |
After this step you could add a custom column. If index = 0 and numberofsubtickets > 0 then 0 else if
index > 0 and numberofsubtickets > 0 then 1 else 1
| Ticket Number | Grade | Description | Index | numberofsubtickets | Number |
| 12345 | A | Apple | 0 | 0 | 1 |
| 54321 | A | Orange | 0 | 3 | 0 |
| 54321 | B | Banana | 1 | 3 | 1 |
| 54321 | C | Peach | 2 | 3 | 1 |
| 54321 | C | Pear | 3 | 3 | 1 |
After this you can perform a group by and sum on the number column and this will give the result your looking for.
Hello ChielFaber !
Thank you very much, it seems to be really that I want!
But could I kindly ask you to explain the step 2 a bit more detailed?
When I right-click on Custom.IndexNew I replicates my ticket number, seems that I'm doing something wrong.
After the step 1 I have this result:Custom.Ticket number Custom.Grade Custom.Description Custom.IndexNew 12345 A Apple 1 54321 A Orange 1 54321 B Banana 2 54321 C Pineapple 3 54321 C Peach 4
FYI: I chose index from 1, from 0 info was a bit mixed up after step 2 in the step 1 (sctions from the article)
