Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors