none
Dax Query help, counting rows based on criteria

    Question

  • Hi Guys.

    I have a dax problem to solve and its currently beyond my dax capabilities. I was hoping someone here could help advise on my best course of action to solve this. We have  a system works on events, and are building a tabular cube against it.

    Background/Overview

    The system works around customers and associating each customer action (event) with a customer.

    -When a customer signs up to the site for the first time, there is a registration event.
    -If a customer subscribes to a newsletter, this is an event.
    -If a customer reads an article, this is an event.
    -if a customer follows an advertiser link on a webpage, this is an event.

    I have a customer Dimension, DimCust that contains all customers & various attributes for those customers.

    I have a fact table, FactEvents that contains all events linked to all the customers in the customer dimension.
    There is also an Event Dimension, DimEvent, which is a list of each possible event and its attributes.
    Of course, there is also a DimDate

    The business question to be answered:

    On the day the customer signed up ( registration event) can we get a count of, and a list of all newsletters they subscribed to (news letter subscribe event)
     

    Schema:

    To help a little , the below shows a very contrived version of the schema:

    Thanks for any help you can provide guys!

    Tuesday, October 22, 2013 9:04 AM

Answers

  • to get the number of newsletters a customer subscribed to you may use this calculation:

    [Nr NewsLetters] := CALCULATE(COUNTROWS('FactEvent'), 'DimEventType'[EventTypeName] = "AssignedToNewsletter")

    the list of which newsletters he subscribed depends on where the name of these newsletters are stored

    hth,
    -gerhard


    - www.pmOne.com -

    • Marked as answer by winstonSmith Friday, October 25, 2013 3:44 PM
    Tuesday, October 22, 2013 4:01 PM
    Answerer
  • Hi Winston,

    If I have understood your requirement correctly then the following approach should work:

    I have created the following dimensions and facts with some mocked-up data:

    DimDate

    DateKey Date
    20120101 01/01/2012
    20120102 02/01/2012
    20120103 03/01/2012
    20120104 04/01/2012
    20120105 05/01/2012
    20120106 06/01/2012

    ...up until 20121231

    DimCustomer

    CustomerID Name
    1 John   Smith
    2 Sarah Jones

    DimEventType

    EventTypeKey EventTypeName
    1 Registration
    2 Newsletter Subscription

    FactEvent

    EventID EventTypeKey CustomerKey DateKey
    1 1 1 20120101
    2 2 1 20120101
    3 2 1 20120101
    4 1 2 20120302
    5 2 2 20120302
    6 2 2 20120401
    7 2 2 20120420

    I have defined the relatonships as described in your post.

    On the FactEvent table, I have defined the following calculated measures:

    CountOfEvents:=CALCULATE(COUNT(FactEvent[EventID]))

    The above calculated measure, [CountOfEvents], is a simple base measure and as you'd expect it will return the count of events.

    CountOfNewRegistrationNewsletterSubscriptionEvents:=CALCULATE(
      [CountOfEvents], 
      CALCULATETABLE(
        VALUES(FactEvent[DateKey]), 
        DimEventType[EventTypeName] = "Registration"
      ),
      DimEventType[EventTypeName] = "Newsletter Subscription"
    )

    The above calculated measure, [CountOfNewRegistrationNewletterSubcriptionEvents], creates a filter context where [CountOfEvents] only considers rows that have the same date as the customer's "Registration" event and where the related event type is a "Newsletter Subscription".

    This is the outcome:

    Now that we have this measure, if you have an event description (I assume this is somewhere in your model, and related to FactEvent), when you drag this on, you will see the newsletter subscriptions that meet this criteria.

    I hope this helps.

    Michael









    Wednesday, October 23, 2013 10:03 AM
    Answerer

All replies

  • to get the number of newsletters a customer subscribed to you may use this calculation:

    [Nr NewsLetters] := CALCULATE(COUNTROWS('FactEvent'), 'DimEventType'[EventTypeName] = "AssignedToNewsletter")

    the list of which newsletters he subscribed depends on where the name of these newsletters are stored

    hth,
    -gerhard


    - www.pmOne.com -

    • Marked as answer by winstonSmith Friday, October 25, 2013 3:44 PM
    Tuesday, October 22, 2013 4:01 PM
    Answerer
  • Hi Winston,

    If I have understood your requirement correctly then the following approach should work:

    I have created the following dimensions and facts with some mocked-up data:

    DimDate

    DateKey Date
    20120101 01/01/2012
    20120102 02/01/2012
    20120103 03/01/2012
    20120104 04/01/2012
    20120105 05/01/2012
    20120106 06/01/2012

    ...up until 20121231

    DimCustomer

    CustomerID Name
    1 John   Smith
    2 Sarah Jones

    DimEventType

    EventTypeKey EventTypeName
    1 Registration
    2 Newsletter Subscription

    FactEvent

    EventID EventTypeKey CustomerKey DateKey
    1 1 1 20120101
    2 2 1 20120101
    3 2 1 20120101
    4 1 2 20120302
    5 2 2 20120302
    6 2 2 20120401
    7 2 2 20120420

    I have defined the relatonships as described in your post.

    On the FactEvent table, I have defined the following calculated measures:

    CountOfEvents:=CALCULATE(COUNT(FactEvent[EventID]))

    The above calculated measure, [CountOfEvents], is a simple base measure and as you'd expect it will return the count of events.

    CountOfNewRegistrationNewsletterSubscriptionEvents:=CALCULATE(
      [CountOfEvents], 
      CALCULATETABLE(
        VALUES(FactEvent[DateKey]), 
        DimEventType[EventTypeName] = "Registration"
      ),
      DimEventType[EventTypeName] = "Newsletter Subscription"
    )

    The above calculated measure, [CountOfNewRegistrationNewletterSubcriptionEvents], creates a filter context where [CountOfEvents] only considers rows that have the same date as the customer's "Registration" event and where the related event type is a "Newsletter Subscription".

    This is the outcome:

    Now that we have this measure, if you have an event description (I assume this is somewhere in your model, and related to FactEvent), when you drag this on, you will see the newsletter subscriptions that meet this criteria.

    I hope this helps.

    Michael









    Wednesday, October 23, 2013 10:03 AM
    Answerer
  • These answers were hugely helpful, thanks a mil guys! I think I should be able to tackle items of this level in future also after your examples.
    Friday, October 25, 2013 3:44 PM