Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
juncco888
Advocate I
Advocate I

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:

output.png

 

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:

tables.JPG

Model and relationships

model.png

 

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
AlexisOlson
Super User
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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.