cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Khramana
Frequent Visitor

Some help with right filtering needed

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 NumberGradeDescription
12345AApple
54321AOrange
54321BBanana
54321CPeach
54321CPear


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!

1 ACCEPTED SOLUTION
ChielFaber
Resolver IV
Resolver IV

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 NumberGradeDescriptionIndex
12345AApple0
54321AOrange0
54321BBanana1
54321CPeach2
54321CPear3

 

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 NumberGradeDescriptionIndexnumberofsubtickets
12345AApple00
54321AOrange03
54321BBanana13
54321CPeach23
54321CPear33

 

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 NumberGradeDescriptionIndexnumberofsubticketsNumber
12345AApple001
54321AOrange030
54321BBanana131
54321CPeach231
54321CPear331

 

After this you can perform a group by and sum on the number column and this will give the result your looking for.

View solution in original post

3 REPLIES 3
ChielFaber
Resolver IV
Resolver IV

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

 

 

ChielFaber
Resolver IV
Resolver IV

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 NumberGradeDescriptionIndex
12345AApple0
54321AOrange0
54321BBanana1
54321CPeach2
54321CPear3

 

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 NumberGradeDescriptionIndexnumberofsubtickets
12345AApple00
54321AOrange03
54321BBanana13
54321CPeach23
54321CPear33

 

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 NumberGradeDescriptionIndexnumberofsubticketsNumber
12345AApple001
54321AOrange030
54321BBanana131
54321CPeach231
54321CPear331

 

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 numberCustom.GradeCustom.DescriptionCustom.IndexNew
12345AApple1
54321AOrange1
54321BBanana2
54321CPineapple3
54321CPeach4



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)

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors