The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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