2 Replies Latest reply on Aug 3, 2012 12:15 PM by gall3on

    Distinct Count of a Higher Tier Dimension


      I'm not sure if I'm the title is the correct way of describing this, but here's the issue:


      I have raw data that contains offices that service certain DMAs and Zip Codes.  I can do a Count(Zip) to get the number of Zip codes each office will service, but when I try to do count(DMA) to show how many DMAs it overlaps, it still counts every zip row in the DMA count, instead of aggregating against just the DMA dimension.  Is there away for me to get around this?


      My data essentially looks like this:


      OfficeOffice Name
      12345New York 1New York00001
      12345New York 1New York00011
      12345New York 1Long Island00111


      So count(zip) tells me that New York 1 Office services 3 zip codes, but I also need to get a count of DMAs it covers, which in this example is 2 DMAs.


      Any thoughts on how I can achieve this?