Join Based on 2 columns (User_Id and Date)

[ChrisBr0]

In PowerBI I have a table of activity log (userId, activity and date). Then I have a user list (userId and last_activity). I created relation between 2 tables with userId column. But I want to also create relation between dates. If I filter specific date range - I want activity logs from this date range and also ONLY users who were active in the specific date range. How to do it?

[aditya0125]

No need for Second Relation i think. Userid column relationship itself filters the Tables along with date column. if that doesn't work then create calendar by using calendar function and create relation between dates.

[SamInogic]

Hi @ChrisBr0 ,

As per our understandings you are looking for a relation between two tables so that you can see filter active user by Date (The users come in between selected date), 

You have already Created a Relation Between the userId now looking for with dates, 

I have crated sample tables your columns names:

Now you looking for to see the Users in between a date range 

For this use a Slicer visual in Power BI report and Drag the Date Column,

Now you can Filter users, 

If we create another relation in same table we get an error as shown in below screenshot,

You don't need a Second Relation for date as Userid column relationship itself filters the Tables along with date column.

Please let me know if we are missing anything in your requirement and if possible you can provide sample pbix so that can check to provide answer for the same.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[rajulshah]

@ChrisBr0 ,

You can create a Date table using CALENDAR DAX function and you can relate this date field with the one in the activitylog table. and you then need to plot date field from the date table in the slicer.