cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Trouble with Measures when values are in seperate tables

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

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Super User

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.

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors