Hi data-heroes
I have not been able to find a working for solution this issue, I hope you can help me.
I'm working with a company which combines data from a mobile application (AppInsights - all navigation, viewing and reading, onboarding before getting an actual user) and their own database (all actual entries and trackings after getting an actual user). All activity has an ID called AppInsights-ID from AppInsights, and UserID from the database.
(For AppInsights, we only have data from July 2021 and there-after).
However, a portion of users change mobile phones or create multiple users. For example this individual, in the screenshot below, has 3 AppInsightsIDs and 7 UserIDs. The IDs overlap in different ways, so we know it's the same user.
I am looking for a way to create an unique AppInsights-ID and UserID (the earliest). So for this person, I would like a column "UniqueAppInsights" with the value of 463aff35-0a80-4e38-8e56-8fe5124ef58a (earliest use 29th of July). And another column with "UniqueUserID" with the value of cf322095-05f2-4271-a2bd-a1664a6b0982 (earliest use 16th of April).
Here is what I tried initially:
UniqueAppInsID =
CALCULATE(
FIRSTNONBLANK('MatchingIDs'[AppInsightsID (Original)], 1),
FILTER(All('MatchingIDs'),
'MatchingIDs'[UserID (Original)] = EARLIER('MatchingIDs'[UserID (Original)])
)
)
Works for most of the users, but not the users like the one I posted in the screenshot above, because not all of the AppIns-IDs and UserIDs overlap directly.
If I could virtually re-create the filter I put in manually in the screenshot above, which isolates all AppInsightsIDs and UserIDs that in any way have overlapped, perhaps a calculcated table, I guess I could do an virtual FIRSTNONBLANK on that.
Any ideas how to solve this?
Much appreciated!
Warm regards
@Anonymous Can you post that data as text? I don't want to type all that data out.
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
