Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am first and foremost hoping to reduce the number of rows I query.
I am querying my HubSpot EmailCampaignEvents table. I need to be able to chart recipient email engagement, but I don't want to have to pull EVERY event type. If an email was OPENED, it was obviously DELIVERED - I don't need the DELIVERED record for that recipient on that email ID.
I WOULD like to prioritize CLICK. So if an email is CLICKED on March 3 and then opened again on March 4, I would like to return the March 3 CLICK Value.
There are 3 ActivityTypes I care about. In order of importance:
CLICK
OPEN
DELIVERED
Below is a small sample of Source Data
Data | |||
EmailCampaignID | RecipientEmail | ActivityType | ActivityDate |
12345 | amanda@amanda.com | Open | 2/22/2022 |
12345 | amanda@amanda.com | Click | 2/23/2022 |
12345 | amanda@amanda.com | Open | 2/24/2022 |
12345 | ashley@ashley.com | Open | 2/21/2022 |
12345 | ashley@ashley.com | Open | 2/28/2022 |
98765 | amanda@amanda.com | Open | 3/2/2022 |
98765 | amanda@amanda.com | Delivered | 3/1/2022 |
98765 | ashley@ashley.com | Delivered | 3/1/2022 |
98765 | ashley@ashley.com | Open | 3/3/2022 |
45678 | amanda@amanda.com | Delivered | 1/1/2022 |
45678 | amanda@amanda.com | Open | 1/2/2022 |
45678 | ashley@ashley.com | Delivered | 1/1/2022 |
And Desired Outcome
Desired Result | |||
EmailCampaign | Recipient | ActivityType | ActivityDate |
12345 | amanda@amanda.com | Click | 2/23/2022 |
12345 | ashley@ashley.com | Open | 2/28/2022 |
98765 | amanda@amanda.com | Open | 3/7/2022 |
98765 | ashley@ashley.com | Open | 3/3/2022 |
45678 | amanda@amanda.com | Open | 1/2/2022 |
45678 | ashley@ashley.com | Delivered | 1/1/2022 |
Solved! Go to Solution.
Simple enough,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Simple enough,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I think I would create a summary table broken down by email ID and recipient. You could then use the summary table as the basis for the relationships and measures within the report.
First you would need to add a new calculated column to the base data for the Activity Type sort order
Activity Type Sort Order = SWITCH( 'Base Table'[Activity Type],
"Click", 1,
"Open", 2,
"Delivered", 3
)
You can then create the summary table
Summary Table = GENERATE( SUMMARIZE('Table', 'Table'[EmailCampaignID], 'Table'[RecipientEmail]),
var currentEmailID = [EmailCampaignID]
var currentRecipient = [RecipientEmail]
var result = SELECTCOLUMNS(
CALCULATETABLE(
TOPN( 1, 'Table',
'Table'[Activity Type Sort Order], ASC,
'Table'[ActivityDate], ASC
),
REMOVEFILTERS('Table'),
'Table'[EmailCampaignID] = currentEmailID && 'Table'[RecipientEmail] = currentRecipient
),
"Activity Type", 'Table'[ActivityType],
"Activity Date", 'Table'[ActivityDate]
)
return result
)
Hi and thank you. This looks like a good back-up plan, but I was actually hoping to reduce my data load on the query side in the first place. I only want to pull in the most recent activity for each email per each user, but prioritize the CLICK if possible.
You could potentially create a ranking column based on the date and the activity type, e.g. take number of days since 1900 and multiply by 5 for a click and 2 for an open. That would give you a column to sort by, but I'm not sure how you could restrict it to 1 row per email per customer. Perhaps one of the group by aggregations would do it ?
Try this measure:
Activity Date =
VAR vDateClick =
CALCULATE (
MAX ( SourceData[ActivityDate] ),
ALLEXCEPT (
SourceData,
SourceData[EmailCampaignID],
SourceData[RecipientEmail]
),
SourceData[ActivityType] = "Click"
)
VAR vDateOpen =
CALCULATE (
MAX ( SourceData[ActivityDate] ),
ALLEXCEPT (
SourceData,
SourceData[EmailCampaignID],
SourceData[RecipientEmail]
),
SourceData[ActivityType] = "Open"
)
VAR vDateDelivered =
CALCULATE (
MAX ( SourceData[ActivityDate] ),
ALLEXCEPT (
SourceData,
SourceData[EmailCampaignID],
SourceData[RecipientEmail]
),
SourceData[ActivityType] = "Delivered"
)
VAR vDateToUse =
COALESCE ( vDateClick, vDateOpen, vDateDelivered )
VAR vResult =
CALCULATE (
MAX ( SourceData[ActivityDate] ),
SourceData[ActivityDate] = vDateToUse
)
RETURN
vResult
In the visual, use the measure [Activity Date]:
Proud to be a Super User!
Hi, and thank you.
I do think this measure will work, but I'd rather reduce my data load on the front, and only bring in the rows I'm looking for. Is there a way to do this at the query editor level?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |