Forum Discussion

alexei7's avatar
alexei7
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

Help with DAX measure

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

Need Help
  • edhans's avatar
    edhans
    7 years ago

    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. 

5 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      alexei7
      Icon for Continued Contributor rankContinued Contributor
      7 years ago

      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's avatar
      alexei7
      Icon for Continued Contributor rankContinued Contributor
      7 years ago

      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

       

       

       

       

       

       

       

       

       

    • PattemManohar's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

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