Trouble with Measures when values are in seperate tables

[juncco888]

Good Afternoon,

Forgive me as I am still a newbie.  Looking for some assistance with a measure. 

I want to create a measure that will display the distinct count of my existing clients who paid money that also show up in the insight query.

Basically  the measure would do this

IF Payments[Amt] >= 1 Then look for their phone number in the insight data, if it is there it counts as 1, if not nothing is added to the count.

Given this output:

The expected result would be 2 since only Jill and John are in insight and have paid money.

Here is what I tried:

Nr who Paid = IF(

    SELECTEDVALUE(Payments[Amt] >= 1,

    CALCULATE(SUM('Insight'[Contact Name]), ALL('Insight'[Contact Name]))),9999)

I have also tried related and relatedtable but am just generally having problems getting measures from values within different tables to work.

Structure of my tables:

Model and relationships

of course the actual data set I am using is far larger than this and has thuosands of records.

Any advice or assistance would be greatly appreciated.  Additionally, if there is a good comprehensive book on creating measures in power BI I would be interested in your recommendations.

Thanks

Kyle

[AlexisOlson · Accepted solution]

Assuming Client has one row per unique client, try this:

Nr who Paid =
COUNTROWS (
    FILTER (
        Client,
        CALCULATE ( SUM ( Payments[Amt] ) ) >= 1 &&
        COUNTROWS ( CALCULATETABLE ( Insight ) ) >= 1
    )
)

This counts the rows in the Client table filtered with two conditions, Payments[Amt] >=1 and Insight has at least one matching row.

The CALCULATE and CALCULATETABLES are used here to do a context transition.

[juncco888]

Thank you so much....   That worked perfect.  I appreciate your explanation as well because it will assist me with some other measures I am working on.

Thanks Again!

Kyle