count of counts

2 Topics

Count of a count in a measure
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
Solved
Anonymous
4 years ago Place DAX Commands and Tips
792Views
0likes
2Comments
Count of Counts
Hello, I am going to try this again, last time I posted it was marked as spam. I think it was because it was my first post and I included a link to a sample .pbix from my OneDrive. I need to make a visualization of the counts of how many times people visited. The visits are represented like this: ContactID Date 1 1/1/2021 1 1/2/2021 2 1/1/2021 3 1/1/2021 3 1/2/2021 3 1/3/2021 4 1/1/2021 5 1/1/2021 5 1/2/2021 5 1/3/2021 5 1/4/2021 5 1/5/2021 6 1/1/2021 6 1/2/2021 7 1/1/2021 8 1/1/2021 9 1/1/2021 9 1/2/2021 9 1/3/2021 I have a measure that will give me the number of visits by ContactID: Visits = COUNTROWS(FILTER('Visits',Visits[ContactID] = Visits[ContactID])) When viewed on a table it looks correct: ContactID Visits 1 2 2 1 3 3 4 1 5 5 6 2 7 1 8 1 9 3 This is where I get lost. Somehow I need to get it to count these counts which could be represented like this: Visits Count of Visitors 1 4 2 2 3 2 4 0 5 1 Which lastly needs to be visualized like this: Any help would be greatly appreciated! Thank you! David
Solved
d2hughes
4 years ago Place DAX Commands and Tips
761Views
0likes
3Comments