cancel
Showing results for
Did you mean:
Highlighted
Member

## Nested Filter

Hi Team,

I have below data where I need to put filter on the customers which has order instance as 5. Once I get those customers I need to sum of the revenue of those customers only but revenue should be summed for all the order instance till 5.

 Customer Order Instance revenue Cust01 1 10 Cust01 2 20 Cust01 3 30 Cust01 4 40 Cust01 5 50 Cust01 6 60 Cust05 1 60 Cust05 2 70 Cust05 3 80 Cust08 1 90 Cust09 2 100 Cust10 1 110 Cust11 1 120 Cust12 1 130 Cust12 2 140 Cust14 1 150 Cust15 1 160 Cust16 1 170

Like in above example only Cust01 has orer instance 5, so the Cust01 shold be filtered out and then sum of revenue for Cust01 where Order Instance is <=5 so in this case revenue should be \$150.

Please note Cust01 is filtered on the base of all those customers which reached upto order instance =5 and then put another filter to get sum of revenue for all order instance <=5, the 6th order instance wont be considered here.

This is kind of nested filter , kindly guide how it can be done in Power BI desktop may be with the help of DAX reference.

Thanks

Swapnil

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## Re: Nested Filter

Hi @srlabhe123,

Based on my test, you can firstly use the formula below to create a new calculate column in your table to indicate if a customer has order instance as 5.

```HasFive =
IF (
COUNTROWS (
FILTER (
ALL ( Table1 ),
Table1[Customer] = EARLIER ( Table1[Customer] )
&& Table1[Order Instance] = 5
)
)
>= 1,
1,
0
)
```

Then you should be able to use the formula below to create a measure to calculate sum of the revenue of those customers only but revenue should be summed for all the order instance till 5.

```Measure =
CALCULATE (
SUM ( Table1[revenue] ),
FILTER ( Table1, Table1[HasFive] = 1 && Table1[Order Instance] <= 5 )
)
```

Here is the sample pbix file for your reference.

Regards

Member

## Re: Nested Filter

it worked with bit work around, thank you

2 REPLIES 2
Super Contributor

## Re: Nested Filter

Hi @srlabhe123,

Based on my test, you can firstly use the formula below to create a new calculate column in your table to indicate if a customer has order instance as 5.

```HasFive =
IF (
COUNTROWS (
FILTER (
ALL ( Table1 ),
Table1[Customer] = EARLIER ( Table1[Customer] )
&& Table1[Order Instance] = 5
)
)
>= 1,
1,
0
)
```

Then you should be able to use the formula below to create a measure to calculate sum of the revenue of those customers only but revenue should be summed for all the order instance till 5.

```Measure =
CALCULATE (
SUM ( Table1[revenue] ),
FILTER ( Table1, Table1[HasFive] = 1 && Table1[Order Instance] <= 5 )
)
```

Here is the sample pbix file for your reference.

Regards

Member

## Re: Nested Filter

it worked with bit work around, thank you

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 44 members 1,107 guests
Recent signins: