Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have a table (Accounts) that contains 5 columns; CustomerID, CustomerName, BankAcctNumber, ProductType and BankAcctBalance.
Note:
How do I calculate the number of customers who have a deposit AND loan acct given the measures below:
No. of loan customers: calculate([No. of Customers],Accounts[ProductType]=”Loan”)
Solved! Go to 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
Hi @atavo,
How do I calculate the number of customers who have a deposit AND loan acct given the measures below:
- No. of Customers:= distinctcount(Accounts[CustomerID]
- No. of accounts:= counta(Accounts[BankAcctNumber]
- No. of deposit customers: calculate([No. of Customers],Accounts[ProductType]=”Deposit”)
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
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
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
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
Thanks 🙂
One method could be to do this within Power Query. You could:
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.
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
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.
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
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |