Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 🙂
Solved! Go to 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
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
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?
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.
Hello Sepehr!
Thank you for your answer. It is not addressing my issue. Please read again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |