Tricky Distinct Count Problem - PowerPivot

[JimMatthews]

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

=calculate ([Total Customers],
    filter(Customers,
        calculate(RegisterType[Type]=1) &&
        countrows(Sales)=0
    )  
)

But I can't get it to work.

Any ideas out there?

 

[scottsen]

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]

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.

Sorry about the link. Try this https://www.dropbox.com/s/07r7jhby6uj9xse/distinct count problem.png?dl=0

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.

=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.

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.

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

Also returns blank.

What I think I need is this

=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]

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?