Is this IF statement possible?

[bonjourposte]

I'm working in financials where we contact our borrowers once a year to ask for their financial reports, net worth statements, etc.  We do a big mail merge for the clients with 1 or 2 loans, but with bigger clients with 3+ loans, we send those individually so we are extra careful that we have their requests right.  I want to automate some of the decision-making around who goes it which pile- "mail merge" or "major borrower".

Rules to see if it's a major borrower:

1) if a "name" (personal or corporate) is on more than 3 loans, their loan goes into the major borrower pile.  The name can be

a) a borrower,

b) a financial contact, or

c) a guarantor.

You can only have one borrower per loan, but a loan can have multiple financial contacts or guarantors.  I know how to group my financial contacts and guarantors into the same respective cell (ie. Financial contacts being comma delimited, and Guarantors being comma delimeted), so ultimately I can get one row per loan.  Can an If statement scan the contents of a cell and look for duplicates across other rows?

Thanks!

[bhanu_gautam · Accepted solution]

@bonjourposte Create a concatenated field for Borrowers, Financial Contacts, and Guarantors: If your data is structured such that Borrower, Financial Contacts, and Guarantors are in separate columns, you could create a new column that combines all of these into a single one for each row, making it easier to look for duplicate names.

CombinedNames = [Borrower] & "," & [FinancialContacts] & "," & [Guarantors]

If you want to break the concatenated column into separate names and count their occurrences, you would need to either use Power Query to split the names into rows or use a more complex DAX formula. A simple way is to count occurrences of a name within the combined column.

Count the number of loans per name: Create a measure or calculated column to count how many times each name appears across all rows (loans).

LoanCountPerName = CALCULATE(
COUNTROWS(Loans),
FILTER(
Loans,
CONTAINSSTRING([CombinedNames], [Name])
)
)

Create another calculated column to flag whether a borrower, financial contact, or guarantor is a major borrower (more than 3 loans).

IsMajorBorrower = IF([LoanCountPerName] > 3, 1, 0)

Finally, you can use this flag to sort your data into two piles (Mail Merge or Major Borrower).

[bonjourposte]

Awesome, thanks, I'll give it a try.