Comparing a dataset to... itself. RRS feed

  • Question

  • Hi All,

    I've just demo'd a PowerPivot spreadsheet to a user and he's very happy about the whole thing :)
    He reckons getting the data in this way will save them weeks in requests for SSRS resource.
    This from simply giving them the ability to see their own data, then slice and dice it etc.

    One question he's asked me is something I'd love to include though so any suggestions here would be great. Say I have a list of sales with a customer number for each one. Even with 3 million entries PowerPivot can show that 500K have a book and 20K have an iPad etc.

    Here's my question: how do we ask something like "Show me how many customers have an iPad but don't have a book?"

    I was thinking of re-importing the sales list with a filter for book and re-import again with a filter for iPad then do the Exclusion rule in a relationship between them. Haven't tried anything yet just thought I'd dip a toe in the water here in the forum first.

    Thanks for reading,

    Wednesday, May 18, 2011 2:59 PM


All replies

  • OddRidge,

    The quick answer is "yes, it can be done". Not very easy, but definitely feasible.

    I am currently preparing a blog post about a similar scenario, which is one of the many applications of many to many relationships (a kind of relationship we, at SQLBI, love) . Nevertheeless, I have some of quick questions for you

    The first is "Do you have a single sales table completely denormalized or you have a classical datamart with customers, products and sales as three separate tables?" I am asking this because my blog post will take for granted that you have to handle a classical dimensional model, otherwise the solution might become quite unfeasible.

    The second one, is something I found interesting in your question (which means intriguing, I'm always searching for challenging data models :)) and it is the "not" in your query. You don't want to know how many users have both an iPad and a book, but want to find customers who don't have a book. Have you already thought about how the user is supposed to pose such a question to the PivotTable? I have some ideas about it, but I'd like to have your feedback before going on.


    Alberto Ferrari
    Wednesday, May 18, 2011 9:58 PM
  • Hi Alberto

    (Must try and change that oddridge thing to a proper name!)


    I have a simple list of say sales ID alongside Customer Number and have been using countrows(distinct...) to provide count of customers so yes denormalised.


    As for posing the question it's a bit annoying actually! Yesterday we were both looking at a list of iPad owners and another list of Book owners and can see the Venn diagram in the data plainly. Just finding a way of doing that systematically is the challenge...


    Thanks for your reply.


    Thursday, May 19, 2011 10:48 AM
  • Alan,

    I really enjoyed writing this post for your scenario. I had to change a whole lot of things from my original one which was about surveys, but I think it was well worth and I can still save the survey model for later posts.

    Take a look here: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/19/powerpivot-basket-analysis-and-the-hidden-many-to-many.aspx

    I cannot say that the final solution is an easy one but... wow, sometimes the power of DAX still amazes me. :)

    Have a good reading.

    Alberto Ferrari
    • Marked as answer by Challen Fu Sunday, May 29, 2011 5:03 AM
    Thursday, May 19, 2011 8:48 PM
  • This is doable. for example, I have a table like this:

    customer ipad book
    a 1 1
    b   1
    c 2  
    d   3
    e 1 1
    g   2
    h 3  

    and then I can define a measure


    COUNTROWS(FILTER('Table3', not iSBLANK([ipad]) && ISBLANK([book]))) and the output looks like this:

    Count of People with Ipad and no book

    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, May 23, 2011 10:26 PM