PDA

View Full Version : How to count the number of customers without deposit accounts



alfred
2017-04-10, 05:03 AM
Hi Ken

Kindly seek your assistance please.

I have a lookup table that contains customer info and a data table that contains deposit account details (including foreign key). These tables are related via the customer id key.

How do I count the number of customers that do not have a deposit account? I understand that if the parent table (i.e. Customer lookup table) row has no corresponding row in the child table (i.e. deposit table), it throws up a blank.

So, after creating this base measure, [No. of customers]:=counta(Customertble[CustomerID]), how can I use this to count the blanks in the deposit data table (i.e. CustID that do not have any corresponding rows in the deposit data table)?

Alfred

Logit
2017-04-12, 08:18 AM
Paste this code into a Routine Module and call with a Command Button :



Option Explicit


Sub CountBlanks()
'Updateby20140310
Dim rng As Range
Dim WorkRng As Range
Dim total As Long
On Error Resume Next


Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", "", WorkRng.Address, Type:=8)
For Each rng In WorkRng
If IsEmpty(rng.Value) Then
total = total + 1
End If
Next
MsgBox "There are " & total & " blank cells in this range."
End Sub


You can also use this formula, editing the targeted range :


=COUNTBLANK(A1:A20)

P3Tom
2017-08-13, 01:47 AM
Hi Ken

Kindly seek your assistance please.

I have a lookup table that contains customer info and a data table that contains deposit account details (including foreign key). These tables are related via the customer id key.

How do I count the number of customers that do not have a deposit account? I understand that if the parent table (i.e. Customer lookup table) row has no corresponding row in the child table (i.e. deposit table), it throws up a blank.

So, after creating this base measure, [No. of customers]:=counta(Customertble[CustomerID]), how can I use this to count the blanks in the deposit data table (i.e. CustID that do not have any corresponding rows in the deposit data table)?

Alfred

You could try:

Customers Without Deposit Account := COUNTROWS ( FILTER ( Customers, COUNTROWS ( RELATEDTABLE ( DeposotAccounts ) ) = 0 ) )