Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Unique Count Help

    Silver Contributor
    Posted Aug 22, 2022 06:38 PM
      |   view attached
    I'm working on a model for marketing metrics. The current challenge is to get a unique count of marketing emails sent to a recipient within XX number of days from the publish date of a specific marketing email. I have a parameter for the user to select the number of days. That number is subtracted from the publish date of the selected marketing email with the goal to find ALL the marketing emails delivered to the recipient in that time frame. Below is my current measure.

    Emails Sent ALL =
    VAR StartDate = SELECTEDVALUE(MarketingEmails[PublishDate])
    VAR Attribution = SELECTEDVALUE('Attribution Length'[Value],90)
    VAR Result =
    CALCULATE(
        COUNT(EmailEvents[Recipient]),
        ALL (EmailEvents[Recipient]),
        EmailEvents[Type]="Delivered",
        Dates[Date]<= StartDate,
        Dates[Date]>= StartDate-Attribution,
        CROSSFILTER(Contacts[email],OrderDetails[Email],Both)
    )
     RETURN
     Result

    In my live dataset there are several instances where a marketing email is shown as delivered to a recipient multiple times on the same date. I've tried to switch up the calculation to count the distinct number of MarketingEmailIDs, but that doesn't work. I've also tried to concatenate fields into a unique field to remove duplicates, but that also didn't work.  I've mocked up the attached PBIX and appreciate any suggestions to fix this DAX measure.

    Nancy 


    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------

    Attachment(s)



  • 2.  RE: Unique Count Help

    Silver Contributor
    Posted Aug 23, 2022 01:56 AM
    Hi - is it as simple as using DistinctCount?

    Emails Sent ALL =
    VAR StartDate = SELECTEDVALUE(MarketingEmails[PublishDate])
    VAR Attribution = SELECTEDVALUE('Attribution Length'[Value],90)
    VAR Result =
    CALCULATE(
        DISTINCTCOUNT(EmailEvents[Recipient]),
        ALL (EmailEvents[Recipient]),
        EmailEvents[Type]="Delivered",
        Dates[Date]<= StartDate,
        Dates[Date]>= StartDate-Attribution,
        CROSSFILTER(Contacts[email],OrderDetails[Email],Both)
    )
     RETURN
     Result


    ------------------------------
    Andrew Simmans
    ------------------------------



  • 3.  RE: Unique Count Help

    Silver Contributor
    Posted Aug 23, 2022 08:37 AM
    If I use DISTINCTCOUNT, the result will always be 1 because it only counts the Recipient once. And if I try to DISTINCTCOUNT the MarketingEmailID, it also results in 1. I need to group by the Recipient and get a unique count of the MarketingEmailID, but I'm not sure how to make that happen.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 4.  RE: Unique Count Help

    Silver Contributor
    Posted Aug 23, 2022 09:22 AM
    It may be that a combination of Countrows with Group by will do this, whatever you want unique you can add to the Group by, in this example Group By creates a "table" with all unique combinations of recipient and marketing id and then Countrows counts the number of rows. I am not sure exactly what you want to count but hopefully this will give you a pointer.
    Emails Sent ALL =
    VAR StartDate = SELECTEDVALUE(MarketingEmails[PublishDate])
    VAR Attribution = SELECTEDVALUE('Attribution Length'[Value],90)
    VAR Result =
    CALCULATE(
        COUNTROWS(GROUPBY(EmailEvents,EmailEvents[Recipient], EmailEvents[MarketingEmailID])),
        ALL (EmailEvents[Recipient]),
        EmailEvents[Type]="Delivered",
        Dates[Date]<= StartDate,
        Dates[Date]>= StartDate-Attribution,
        CROSSFILTER(Contacts[email],OrderDetails[Email],Both)
    )
     RETURN
     Result


    ------------------------------
    Andrew Simmans
    ------------------------------



  • 5.  RE: Unique Count Help

    Silver Contributor
    Posted Aug 25, 2022 09:32 AM
    I finally figured this out. The step that was missing was resetting the Marketing Email table to ALL. And since I have multiple relationships from the Dates table, it required me to establish the one to use for this calculation. This measure provides the correct answers. Thanks @Andrew Simmans for your assistance.

    Emails Sent ALL =
    VAR StartDate = SELECTEDVALUE(MarketingEmails[PublishDate])
    VAR Attribution = SELECTEDVALUE('Attribution Length'[Value],90)
    VAR Result =
    CALCULATE(
        COUNT(EmailEvents[Recipient]),
        ALL (EmailEvents[Recipient]),
        ALL(MarketingEmails),
        EmailEvents[Type]="Delivered",
        Dates[Date]<= StartDate,
        Dates[Date]>= StartDate-Attribution,
        USERELATIONSHIP(EmailEvents[Created Date],Dates[Date]),
        CROSSFILTER(Contacts[email],OrderDetails[Email],Both)
    )
     RETURN
     Result

    ​​

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------