cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Marc-Alexis Frequent Visitor
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 childrenRevenue
AAA010
BBB020
CAA130

 

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?

 

Thank you for your help! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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.

Capture.PNG

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
Sepehr07 Frequent Visitor
Frequent Visitor

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

@Marc-Alexis  

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

Capture.PNG

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.
Marc-Alexis Frequent Visitor
Frequent Visitor

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

Hello Sepehr!

 

Thank you for your answer. It is not addressing my issue. Please read again.

Marc-Alexis Frequent Visitor
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?

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 5,123 guests
Please welcome our newest community members: