Dax Filter function - Determine customers who have a deposit AND loan acct

[atavo]

Hi

I have a table (Accounts) that contains 5 columns; CustomerID, CustomerName, BankAcctNumber, ProductType and BankAcctBalance.

Note:

1. ProductType column can have the values "Deposit" or "Loan"
2. BankAcctNumber column does not have any duplicate values
3. CustomerID and CustomerName column can have duplicate values i.e. same customer can have a deposit account and loan account.

How do I calculate the number of customers who have a deposit AND loan acct given the measures below:

1. No. of Customers:= distinctcount(Accounts[CustomerID]
2. No. of accounts:= counta(Accounts[BankAcctNumber]
3. No. of deposit customers: calculate([No. of Customers],Accounts[ProductType]=”Deposit”)

4. No. of loan customers: calculate([No. of Customers],Accounts[ProductType]=”Loan”)

[v-ljerr-msft]

Hi @atavo,

---

How do I calculate the number of customers who have a deposit AND loan acct given the measures below:

1. No. of Customers:= distinctcount(Accounts[CustomerID]
2. No. of accounts:= counta(Accounts[BankAcctNumber]
3. No. of deposit customers: calculate([No. of Customers],Accounts[ProductType]=”Deposit”)

4. No. of loan customers: calculate([No. of Customers],Accounts[ProductType]=”Loan”)

---

According to your descriptions above, you should be able to simply use the formula below to calculate the number of customers who have a deposit AND loan acct. 

No. of customers with deposit and loan acct(s) = [No. of accounts] - [No. of Customers]

Regards

[atavo]

Thanks v-ljerr-msft

A brilliant suggestion...however does not work if customer has more than 1 bank acct. Ex.

Customer 1 - Dep_acct_1

Customer 1 - Dep_acct_2

Customer 2 - Dep_acct_3

Customer 3 - Dep_acct_4

Customer 1 - Loan_acct_1

Customer 4 - Loan_acct_2

Customer 5 - Loan acct_3

[No. of accounts] - [No. of customers]<>[No of customers with deposit and loan accts] i.e. 7-5<>1

In any case, wondering if you know how to use virtual tables, intersect them and use these to filter the base measure [no of customers]...please see my reply to Ross

[v-ljerr-msft]

Hi @atavo,

---

A brilliant suggestion...however does not work if customer has more than 1 bank acct. Ex.

Customer 1 - Dep_acct_1

Customer 1 - Dep_acct_2

Customer 2 - Dep_acct_3

Customer 3 - Dep_acct_4

Customer 1 - Loan_acct_1

Customer 4 - Loan_acct_2

Customer 5 - Loan acct_3

[No. of accounts] - [No. of customers]<>[No of customers with deposit and loan accts] i.e. 7-5<>1

---

In this scenario, you should be able to simply use the formula below to make it work.

No. of customers with deposit and loan acct(s) = [No. of deposit customers] + [No. of loan customers] - [No. of Customers]

Regards

[atavo]

The only customer that qualifies is Customer 1...

[v-ljerr-msft · Accepted solution]

Hi @atavo,

The only customer that qualifies is Customer 1...

---

Yes, I understanded that. 

[No. of deposit customers] is 3 (Customer 1, 2 , 3)

[No. of loan customers] is 3 (Customer 1, 4, 5)

[No. of Customers] is 5 (Customer 1, 2 , 3, 4, 5)

So [No. of customers with deposit and loan acct(s)] = 3 + 3 - 5 = 1

Regards

[atavo]

Thanks 🙂

[Anonymous]

One method could be to do this within Power Query.  You could:

1. Call your table 'MasterTable'
2. Copy your MasterTable query into another query by naming MasterTable as the source.  Then filter so it only contains Deposit entries.  Call this 'DepositTable'
3. Do the same as 2 but for loans. Call this 'LoanTable'
4. Create a new Table where you will perform a table merge of DepositTable and LoanTable using an InnerJoin, which would leave only the rows that have both a deposit and a loan.

Now you will have a table containing rows where you had an entry in your Deposit table and Loan table, based on whichever column you selected as your join method.

[atavo]

Thanks Ross,

Can dax solve this problem with just the one table (i.e. Accounts)?

Ultimately, what I am looking at, is having these 3 measures side-by-side:

1. No. of customers with deposit and loan acct(s)

2. No. of customers with deposit acct(s) but no loan accts

3. No. of customers with loan acct(s) but no deposit accts

[Anonymous]

If i had to do it all within DAX and all within a single table i'd create multiple columns.  Those columns would be

(1).  Expression to count how many times the current row's customer has Deposit accounts

(2).  Expression to count how many times the current row's customer has Loan accounts

(3).  Expression that is a 1 if both (1) and (2) columns are 1 or higher, 0 if not.

(4).  Expression that is 1 if (1) is 1 or greater and (2) is 0.  0 if not.

(5).  Expression that is 1 if (0) is 0 and (2) is 1 or greater.  0 if not.

Then to get your values, you can have a metric that is a calculate statement on distinct count of customer where your (3), (4), or (5) metric is 1 (choosing (3) (4) or (5) based on the metric you are checking for.

[atavo]

Thanks Ross,

Don't really want to deal with multiple columns.

Still a newbie with dax, but this is what I want to do to determine "No of customers with both deposit and loan accts" using dax measure:

1. Create two virtual tables for customers with deposit accts (Accounts[ProductTyp]="Deposit") and customers with loan accts (Accounts[ProductTyp]="Loan").

2. Intersect these virtual tables at the Accounts[CustomerID] Column

3. Use this new 1 Column table to filter the base measure [No of customers] and hence calculate above requirement.

4. Use "if" function with new measure in #3 to determine the other measures I mentioned.

...Just not well versed with dax syntax