Looking for correlation between data in two seperate tables

[Ken_Powerless]

Setup:

I'm working with two tables.  Both Tables have the fields User, Notes and Dates.  

Both Tables can have multiple instances of the same user.

Problem:

A user can be in [table one], [table two], or both [table one] and [table two].

If a user is in table one and table two, and the date in table two is on or after the date of table one by seven days or less, then there is a correlation between the user being in table one and table two and I wish to pull that data and model it in Power BI.

Tried:

I tried creating a [new table] with unique values I pulled from [table one], then create a one to many relationship from that [new table] to [table one] and then also from the [new table] to [table two] between both the tables.  To no avail

I have not been able to get this to work even for that without even worrying about the date parameters.

Any advice would be appreciated. 

Also, I'm still very new to Power BI so feel free to talk down to me a bit.

[v-sihou-msft]

@Ken_Powerless

According to your description, you want to INNER JOIN tables and filter the records where the interval between two dates is less than 7 days. Right?

You can create a calculated table to crossjoin two tables with filters applied. Please refer to formula below:

Table =
FILTER (
    CROSSJOIN ( 'Table1', 'Table2' ),
    'Table1'[User] = 'Table2'[User]
        && DATEDIFF ( 'Table1'[Date], 'Table2'[Date], DAY ) <= 7
)

If this still can't achieve your goal, please share some sample data.

Regards,

[MarcelBeug]

Power BI has 2 flavours of data structuring: the query editor with the M language (a.k.a. Power Query) and data modeling using DAX.

Roughly M is the first part to get and transform data to your data model; then comes DAX for further modeling, typically - but by no means limited to - aggregating data into measures.

Your requirement seems to be in the big grey area covered by both M and DAX.

As my focus is on M: this video explains how it can be done in M, starting in the query editor with 2 tables.
The video includes 2 alternative table joins as it was not clear to me what is required.

Any DAX solution I leave to the DAX specialists.