Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI masters!
Could I kindly ask you to give some advise how to set up right filtering?
I have a table that contains of several columns.
Example:
Ticket Number | Grade | Description |
12345 | A | Apple |
54321 | A | Orange |
54321 | B | Banana |
54321 | C | Peach |
54321 | C | Pear |
Sum result shows 5
Briefly, there are 3 columns as an example: Ticket Number, Grade, Description.
Grade shows whether is a master ticket (Grade is always A for them) or a subticket (Grade is never A).
If ticket has no subticket - I need still to keep it as 1 item. Example ticket is 12345.
If ticket has subtickets like ticket 54321 (subticket's Grade is never A) - I need only to take subticket items of it. In other words, - to ignore parent ticket as an item for such case.
I think that solution is to type 1 of my conditials are met and 0 if not.
But I have some difficulties how to make the right DAX conditional expression...
Could you please advise?
Thanks a lot in advance!
Solved! Go to Solution.
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.
Personally I would change the code by Reza Rad to start with 0. I believe it will then look like this:
Table.AddIndexColumn([Count],"Index",0)
After that with step2 you need to again group all rows. You could follow step1 from Reza again. Add All rows and then add a new aggregation: max(Index).
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.