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.
Hi,
I'm trying to create a measure which aggregates on two conditions.
My data can be simplified to look like this:
Table A:
PersonID
PersonStatus
Table B:
PersonID
PaymentID
PaymentAmount
PersonID and Payment ID are unique keys and the tables are joined 1-many (1 from table A to many in table B)
I want a measure to count
all PersonIDs
who have PersonStatus which is not blank and
whose TOTAL PaymentAmount is >0
I feel like this should be possible without creating the custom column in Table A, and by using Calculate, but I can't quite work out the right formula.
Can someone help?
Much appreciated,
Alex
Solved! Go to Solution.
Is this what you need?
Persons with Payments = CALCULATE( DISTINCTCOUNT(Persons[PersonID]), FILTER(Persons,NOT(ISBLANK(Persons[PersonStatus]))), FILTER(Payments,SUM(Payments[PaymentAmount]) > 0 ) )
See this file for the table data I mocked up.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIs this what you need?
Persons with Payments = CALCULATE( DISTINCTCOUNT(Persons[PersonID]), FILTER(Persons,NOT(ISBLANK(Persons[PersonStatus]))), FILTER(Payments,SUM(Payments[PaymentAmount]) > 0 ) )
See this file for the table data I mocked up.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans - does the job perfectly, thank you.
@PattemManohar - there is a valid relationship, this even seems to work for me using your version of the test data (which didnt have PaymentID as a unique key, as mine did). Thanks for your time in looking into this.
@alexei7 Some sample data will be really helpful to test and resolve the scenario....
Proud to be a PBI Community Champion
@alexei7 In the data you have posted, there is no valid relationship between two tables on PersonID.
I've tried to achieve this through "Power Query Editor" based on the test data I've created below...
Steps in Power Query...
let Source = Table.NestedJoin(Persons,{"PersonID"},Payments,{"PersonID"},"Payments",JoinKind.LeftOuter), #"Expanded Payments" = Table.ExpandTableColumn(Source, "Payments", {"PaymentID", "Amount"}, {"Payments.PaymentID", "Payments.Amount"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded Payments", each [PersonStatus] <> null and [PersonStatus] <> ""), #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"PersonID", "PersonStatus"}, {{"TotalAmount", each List.Sum([Payments.Amount]), type number}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [TotalAmount] > 0) in #"Filtered Rows"
Finally, a simple measure from the new table created above...
TotalPersons = DISTINCTCOUNT(PersonPayments[PersonID])
Please try and let me know if it breaks for any of your case..... and post the same sample data to replicate...
Proud to be a PBI Community Champion
Table A:
PersonID Status
1 Active
2 Active
3
4 Inactive
Table B
PaymentID PersonID PaymentAmount
1 1 5
2 2 5
3 1 10
4 3 10
5 7 10
6 9 -5
7 1 5
8 5 5
9 1 10
10 2 5
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |