# Tricky Distinct Count Problem - PowerPivot

#### JimMatthews

##### New Member
I have a distinct count problem I can't get my head around. See the following data model image

I have a simple distinctcount (sales[customer number]) that tells me how many customers there are.
I can create a pivot table and put "register type" in rows and distinct count in values. This tells me how many customers purchased from each register type.
What I want to do is find out how many customers DID NOT purchase from each register type (customers can by from none or more register types)

So I am thinking I need something like this.

Customers not purchased from Type 1
Code:
``````=calculate ([Total Customers],
filter(Customers,
calculate(RegisterType[Type]=1) &&
countrows(Sales)=0
)
)``````
But I can't get it to work.

Any ideas out there?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### scottsen

##### Well-known Member
Data model pictures didn't come through.

I'm wondering if =CALCULATE([Total Customers], ALL(RegisterType)) - [Total Customers] would give you what you want?

#### JimMatthews

##### New Member
Hi Scott

I don't think that will do it. Take an extreme example. I have 100 customers. 95 of them have purchased through register 1 and 90 of them have purchased through register 2. If I were doing this in RDBMS I would create a query, join the list of all customers with the list for register 1, and find out the 5 customers that didn't purchase through that register. Repeat for register 2. You will end up with 2 lists, and there is no guarantee that there will be any customers on both lists.

So I am thinking this is a bit like a many to many problem. I am trying to push a filter that iterates the customer table and checks all customers to see if they are "absent" as a register 1 customer. So in the filter, I first filter for register type =1 (this should flow through to the sales table giving me a list of all customers that "HAVE" purchased through register 1. Then my Calculate(countrows(sales))=0 kicks in to see if there are any records. zero records means they have never purchase from this register. So it would be like this.

Code:
``````=CALCULATE([Customer Count],
filter(
Customers,
RegisterType[Type]=1 &&
CALCULATE(
COUNTROWS(sales)
) = 0

)
)``````

Trouble is I get this error

Calculation error in measure 'Sales'[Customers Never Buying Delivery]: The value for column 'Type' in table 'RegisterType' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

so this is telling me that I have no row context with RegisterType[Type], which makes sense. So then I tried to push this part of the formula back up the stack, and tried this.

Code:
``````CALCULATE(
CALCULATE([Customer Count],
RegisterType[Type]<>"1"
) &&     filter(
Customers,
CALCULATE(
COUNTROWS(sales)
) = 0
)
)``````

I kind of like this formula (except it returns blank). The first portion returns the count of customers that have purchased on any register except 1 - so that is good. But then it falls over itself.

Then I tried this.

Code:
``````=CALCULATE([Customer Count],
filter(RegisterType,
RegisterType[Type]<>"1"
),
filter(
Customers,
CALCULATE(COUNTROWS(sales)) = 0
)
)``````

Also returns blank.

What I think I need is this
Code:
``````=CALCULATE([Customer Count],
filter(RegisterType,
RegisterType[Type]<>"1"  &&
filter(
Customers,
CALCULATE(COUNTROWS(sales)) = 0
)
)
)``````
but DAX doesn't like it. It says

Calculation error in measure 'Sales'[Customers Never Buying Delivery]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

So back to a statement of the problem. I want to count all customers that when a filter is applied to the RegisterType[Type]= 1, then counts all customers that don't have a record in the sales table.

#### scottsen

##### Well-known Member
I'm sure we can work this out You seem to be flirting all over the place with a good solution. Time to bring the date home for a night cap

Can you give me a small snippet of expected output? I want to be sure I understand what you mean by "find out how many customers DID NOT purchase from each register type"

Do you want a specific measure for JUST RegisterType=1? Or was that just... trying to get something to work?

Replies
0
Views
58
Replies
0
Views
1K
Replies
0
Views
88
Replies
1
Views
101
Replies
3
Views
136

1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...