Filter a DAX on a condition involving more than one row

[Marc-Alexis]

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?

Thank you for your help! 🙂

[v-piga-msft · Accepted solution]

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

[Marc-Alexis]

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?

[Anonymous]

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

[Marc-Alexis]

Hello Sepehr!

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