Help with DAX measure

[alexei7]

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

[edhans · Accepted 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. 

[alexei7]

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

[PattemManohar]

@alexei7 Some sample data will be really helpful to test and resolve the scenario....

[PattemManohar]

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

PersonsPayments

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

[alexei7]

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