cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Filter a DAX on a condition involving more than one row

Dear all,

I struggle building a DAX formula that filters on a condition involving multiple rows sharing a common field.

Here is a simplified example of my chalenge:

 Reservation # Customer ID # of children Revenue A AA 0 10 B BB 0 20 C AA 1 30

I am trying to formulate a DAX by which I sum the revenue of all rows with a Customer ID for which the sum of children is above 0.

In our example:

Reservation C = 1 child

Therefore Customer ID "AA" = 1 child --> >0 --> meets the condition

All rows with Customer ID "AA" should be included.

Reservation A and Reservation C share the same Customer ID "AA"

So the revenue of both Reservations A and C should be included

The expected result is therefore SUM of "Revenue" of Reservation 1+Reservation 3 = 10+30 = 40

Any DAX suggestion?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Filter a DAX on a condition involving more than one row

Hi @Marc-Alexis ,

By my tests, you could create a calculated column and a measure to achieve your desired output.

```Column =
IF (
CALCULATE (
SUM ( 'Table'[# of children] ) >= 1,
FILTER ( 'Table', 'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] ) )
),
1,
0
)
Measure =
CALCULATE ( SUM ( 'Table'[Revenue] ), FILTER ( 'Table', [Column] = 1 ) )```

Here is my test output.

If you still need help, feel free to ask.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Frequent Visitor

## Re: Filter a DAX on a condition involving more than one row

SumOfRevenue = Calculate(sum(table.[Revenue]), Filter(table,table[#Children] ="0"))

try this, is not worked let me know

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

Community Support Team

## Re: Filter a DAX on a condition involving more than one row

Hi @Marc-Alexis ,

By my tests, you could create a calculated column and a measure to achieve your desired output.

```Column =
IF (
CALCULATE (
SUM ( 'Table'[# of children] ) >= 1,
FILTER ( 'Table', 'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] ) )
),
1,
0
)
Measure =
CALCULATE ( SUM ( 'Table'[Revenue] ), FILTER ( 'Table', [Column] = 1 ) )```

Here is my test output.

If you still need help, feel free to ask.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Hello Sepehr!

Frequent Visitor

## Re: Filter a DAX on a condition involving more than one row

Hello Cherry,

Your suggestion is definitely heading into the direction I want to get to. Do you think that the condition that you are currently including in the calculated column (IF ( CALCULATE ( SUM ( 'Table'[# of children] ) >= 1, FILTER ( 'Table', 'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] ) ) ), 1, 0 )) could be evaluated directly in the measure without having to create an additional column?

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 BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 5,123 guests
Recent signins: