Forum Discussion

Ken_Powerless's avatar
Ken_Powerless
New Member
9 years ago

Looking for correlation between data in two seperate tables

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.

Need Help

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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,