Count of a count in a measure

[bdmichael09]

Hello PBI community,

I'm trying to accomplish something that I've been struggling to get to work for a number of hours now and reading other threads has not inspired me to a measure that works.

I have a table of active borrowers (Portfolio). I have a table of new applications (Leads). My end goal is slightly more complicated but what I am trying to produce is simply a card that shows 'count of active borrowers with new leads' and a card of "count of borrowers without new leads".

The portfolio table has a ServicedLoanNumber, start date, and end date fields. The Leads table has the new application number (LoanNumber), the lead date and the ServicedLoanNumber fields. The two tables have a relationship on the ServicedLoanNumber. I understand that I could use a calculated column to get the count of occurences in the Leads table but for what I'm working towards in the end, the column will not work.

I thought I could do something like

CALCULATE( COUNT( Portfolio[ServicedLoanNumber] ), COUNTROWS( Portfolio[ServicedLoanNumber] ) = 0 )

to be a measure of "count of borrowers without new leads" but PBI doens't seem to like that much. I've tried some other things too but I haven't exactly been saving each failed method in separate measures so it's hard to say what exactly I've tried.

Any help is appreciated. 

Just as an example of what I expect, with this made up sample below, I would expect to get 3 as an output of "count of active borrowers with new leads" and 2 as an output of "ount of active borrowers without new leads"

ServicedLoanNumber	JoinDate	LeaveDate
1	1/1/2022	6/1/2022
2	1/1/2021
3	1/1/2020
4	1/1/2020
5	1/1/2020

LoanNumber	ServicedLoanNumber	CreateDate
100	1	4/1/2022
101	1	4/30/2022
102	1	5/15/2022
103	2	2/1/2022
104	3	3/1/2022
105	3	4/1/2022
106	2	5/1/2022
107	2	1/1/2022
108	2	3/15/2022
109	2	4/15/2022

[v-mengzhu-msft · Accepted solution]

Hi @bdmichael09 ,

Please try the following measure:

Count of active borrowers with new leads = COUNTROWS(INTERSECT(SELECTCOLUMNS('Portfolio',"pro",'Portfolio'[ServicedLoanNumber]),SELECTCOLUMNS('Leads',"Lea",'Leads'[ServicedLoanNumber])))

count of active borrowers without new leads = COUNTROWS(EXCEPT(SELECTCOLUMNS('Portfolio',"por",'Portfolio'[ServicedLoanNumber]),SELECTCOLUMNS('Leads',"Lea",'Leads'[ServicedLoanNumber])))

Then you can get effect you want to achieve, as the image shows:

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Jos_Woolley]

Hi,

So each ServicedLoanNumber only appears once in the Portfolio table?

If so, for count of active borrowers without new leads you could try:

Count of active borrowers without new leads =
COUNTROWS(
    EXCEPT(
        SELECTCOLUMNS( Portfolio, "LoanNo", Portfolio[ServicedLoanNumber] ),
        SELECTCOLUMNS( Leads, "LoanNo", Leads[ServicedLoanNumber] )
    )
)

You could then simply subtract that from the total number of rows in the Portfolio table to obtain your other metric.

Regards