Best way to relate/join 3 tables on Power BI (To filter rows/values).

[gunasai]

Hi guys,

I have the following 3 tables,

Table 1: Users

ID	User Name
1	abc
2	efg
3	xyz

Table 2: Time_track

ID	User Name	Login Date	Total_log_time (min)	No_of_sessions
1	abc	2021-08-02	435	9
1	abc	2021-08-03	335	2
1	abc	2021-08-04	265	6
3	xyz	2021-08-02	263	4
3	xyz	2021-08-03	264	5
3	xyz	2021-08-04	265	4

Table 3: Picks table

Order Number	Item number	Qty_picked	PickTime	User_id
1111	1	20	2021-08-02 10:00:00	abc
1111	2	30	2021-08-02 11:00:00	abc
1111	3	40	2021-08-02 12:00:00	abc
2222	4	50	2021-08-03 7:00:00	abc
2222	5	60	2021-08-04 8:00:00	abc
3333	6	10	2021-08-02 10:00:00	xyz
3333	7	20	2021-08-02 12:30:00	xyz
4444	8	30	2021-08-03 1:00:00	xyz
4444	9	40	2021-08-04 4:00:00	xyz

Now I can relate Table 1 (Users table) and Table 2 (Time_track table) in a "1 to many" relationship on "ID" to filter rows OR Table 1 (Users table) and Table 3 (Picks table) in a "1 to many" relationship on "ID" to filter rows

1) How do I relate Table 2 (Time_track table) and Table 3 (Picks table) to filter rows while both of these being related to the Table 1 (Users table)??

2) What if I want to show the following in Power BI?

ID	User Name	Login Date	Total_log_time (in min)	No_of_sessions	Qty_picked
1	abc	2021-08-02	435	9	90
1	abc	2021-08-03	335	2	50
1	abc	2021-08-04	265	6	60

Any suggestions would be helpful.

Thanks in advance!

[v-yalanwu-msft]

Hi, @gunasai ;

As  AlexisOlson said, you could create another table then create a relationship about date.

or you could create a measure as follows:

Measure =
CALCULATE (
    SUM ( 'Table (3)'[Qty_picked] ),
    FILTER (
        'Table (3)',
        [User_id] = MAX ( 'Table (2)'[User Name] )
            && DATEDIFF ( [PickTime], MAX ( 'Table (2)'[Login Date] ), DAY ) = 0
    )
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

Set it up like this:

With this, it should be drag-and-drop into a table visual: