locked
Counting the distinct column values in the same selected period RRS feed

  • General discussion

  • Hi,

    I need to count the distinct customer accesses to a e-commerce site, composed of some sections, in the same period
    (year, month, day, weeknumber, ...).
    Fe, a customer can access more time in the same day and so I could have the same dates but with different times.
    I'd like to consider the last site section accessed in the same selected period.
    For this goal, I've three tables: Customers (ID + Description), Time (datekey, year, month, day, weeknumber),
    SiteSections (ID + Description) and Fact_Accesses (ID + Customer_ID, SiteSection_ID, AccessDateTime).

    I think to use the Count function inside the Calculate expression that uses a Filter.

    Fe, I could have these data for the Fact_Accesses:

    ID      Customer_ID      SiteSection_ID      AccessDateTime
    1        Cust_01             Section01             10/01/2013 09:00:00
    2        Cust_01             Section05             10/01/2013 11:00:00
    3        Cust_02             Section05             10/01/2013 14:00:00

    so, if I select the 10/01/2013 day and put SiteSection in the row label of the pivot table, I'd like to count 1 row for Cust_01 + Section01,
    1 row for Cust_02 + Section05.
    Moreover, I'd like to not see any values respect to Cust_01 + Section01 or see a zero value for this data combination.

    I've performed some proofs without the expected results.

    Any suggests to me, please? Thanks


    Is it possible to move this thread to the SQL Server PowerPivot for Excel, please?
    Friday, January 11, 2013 11:04 PM

All replies