Forum Discussion
littlemojopuppy
6 years agoCommunity Champion
Grouping Eliminating Duplicates
Good morning! I'd like to ask for some help with something... I have a table of transactions. I am summarizing them by IdenityID, Date, Time and Marketing Source. Problem is that there are some...
- 6 years ago
Ok...this works
Marketing Count Test = VAR LoginTransactions = GROUPBY( ADDCOLUMNS( IdentityLog, "ModifiedTime", TIME( HOUR(IdentityLog[CreatedTime]), MINUTE(IdentityLog[CreatedTime]), 0 ) ), IdentityLog[IdentityId], IdentityLog[MarketingSourceLogId], IdentityLog[CreatedDate], [ModifiedTime] ) VAR UniqueLogins = SUMMARIZE( LoginTransactions, IdentityLog[IdentityId], IdentityLog[CreatedDate], [ModifiedTime], "MarketingSource", MAX(IdentityLog[MarketingSourceLogId]) ) RETURN UniqueLoginsBut this seems overly complicated. I could achieve the same result with SUMMARIZECOLUMNS if I could truncate seconds from the time. Could anyone please offer some guidance on how to achieve that?
- 6 years ago
This is what I was looking for!
Active Users by Marketing Source Earliest Attribution = VAR LoginTransactions = ADDCOLUMNS( SUMMARIZECOLUMNS( IdentityLog[IdentityId], IdentityLog[CreatedDate], "Created Time", MIN(IdentityLog[CreatedTime]) ), "MarketingSource", LOOKUPVALUE( IdentityLog[MarketingSourceLogId], IdentityLog[IdentityId], IdentityLog[IdentityId], IdentityLog[CreatedDate], IdentityLog[CreatedDate], IdentityLog[CreatedTime], [Created Time] ) ) RETURN COUNTROWS(LoginTransactions)
littlemojopuppy
6 years agoCommunity Champion
Thanks for looking at it!
Dumping an Excel file here.
littlemojopuppy
6 years agoCommunity Champion
This is what I was looking for!
Active Users by Marketing Source Earliest Attribution =
VAR
LoginTransactions =
ADDCOLUMNS(
SUMMARIZECOLUMNS(
IdentityLog[IdentityId],
IdentityLog[CreatedDate],
"Created Time",
MIN(IdentityLog[CreatedTime])
),
"MarketingSource",
LOOKUPVALUE(
IdentityLog[MarketingSourceLogId],
IdentityLog[IdentityId],
IdentityLog[IdentityId],
IdentityLog[CreatedDate],
IdentityLog[CreatedDate],
IdentityLog[CreatedTime],
[Created Time]
)
)
RETURN
COUNTROWS(LoginTransactions)