DateDiff Array with a Many to Many join

[nikkilovesmath]

I have a table, Events, that has an EventId and the EventDate. EventId is unique in the table.

I have another table, AffectedUsers, that has the UserId and an EventId.  A User can be affected by multiple Events, and an Event can affect multiple Users.  However, the user-event combinations are unqiue for this table.  I know already that the max number of Events that affected a user is about 35, and several Users have only 1 event that affected them.  

AffectedUsers

UserId	EventId
abc456	123
def321	123
def321	246
def321	789

Events

EventId	EventDate
123	5/21/2023
246	5/26/2023
789	5/29/2023

For each User, I need to know the interval between Events and from there find the median.   Ideally I was going to make an ordered array of event dates per UserID and find the interval between each datetime in the array using EARLIER, but that was throwing some complicated errors I coulnd't find support for (I think because some Users only have one Event), so I thought I'd go another route...

First, I created two Dim tables, UniqueEvents and UniqueUsers, that just have the unique EventIds and UerIds, respectively.  Then I removed the relationships between the two main tables and had them both point to the derived dim tables. 

I was trying to use LOOKUPVALUE to even just get the Event Dates into the AffectedUsers table as a calculated field, but since it is many-to-many relationship, I got a "circular dependency error."  When I tried using RELATED, the error was the column doesn't have a relationship in the current context, although the Affected User table has a relationship to the uniqueEvent table, and the uniqueEvent Table has a relationship to the Events table.  RELATED did not work when the relationships was direct between the two tables, because of the many to many relationship, it would only work with an aggregate such as the min event date per user, which isn't what I'm looking for.

What is a good method in PowerBI Desktop to calculate the datediff array and find this median time interval?

[Ashish_Mathur]

Hi,

Share a few more rows in the 2 tables, explain your methodology and show the expected end result.  You may do so in an MS Excel file.  I'll understand your logic from there and translate that into DAX/M code.

Share the download link of the MS Excel file.

[nikkilovesmath]

Hi Ashish:

Please find below.  

I considered going back to Kusto and using its joining conditions, but I need to be able to use PowerBI's filtering (there are some other elements in the Events table like source, owner, so on), so a calculated table would be better than a static stable from Kusto.
Thank you!

User Events

UserId	EventId
abc456	123
def321	123
def321	246
def321	789
def321	963
def321	369
def321	765
ghi777	123
ghi777	246
ghi777	963
kjl888	246
kjl888	765

Event Details

EventId	EventDate
123	5/1/23
246	5/14/23
789	5/15/23
963	5/20/23
369	5/26/23
765	5/31/23

Desired Output

UserId	EventDateArray	IntervalArray	MedianInterval
abc456	<5/1/2023>	<null>	null
def321	<5/1/2023, 5/14/2023, 5/15/2023, 5/20/2023, 5/26/2023, 5/31/2023>	<13,1,5,6,5>	5
ghi777	<5/1/2023, 5/14/2023, 5/20/2023>	<13,6>	9.5
kjl888	<5/14/2023, 5/31/2023>	<17>	17

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  The interval array is wrong in row 2.  A 5 is mssing from there.  I cannot correct that.  Hope you can take it up from here.

Hope this helps.