Logical clause for a join ?

[EFont]

Hello !

I would like to get a list of active years per users, based on the years between their start and end dates.

My Issue is that I do not have the possibility to add Stored Procedures on my provider's database.

So, I am trying in DAX instead

First, here's an idea of how I would have done it in SQL :

Select u.USerID, y.Year
From vwUsers u left join vwYears y
on y.Year between (u.UserDateHired and coalesce(u.UserDateTerminated, Date.Now()))

Through DAX, I created the following :

ctUserActivity =
Var curDay = Today()
Return SELECTCOLUMNS(vwUsers, "UserID", [UserID],  "DateStarted", [UserDateAdded], "DateEnded", COALESCE([UserDateInactivated], curDay))

So I have my user info ready to join with my Calendar table... But I don't see how to state a logical clause for the join

Here is a data sample of what I would like to obtain:

UserID	Activity
101010	2010
101010	2011
101010	2012
101010	2013
101010	2014
101010	2015
101010	2016
202020	2019
202020	2020
202020	2021
202020	2022
202020	2023
303030	2021
303030	2022
303030	2023

Please help !

Thank you,

Erik

[CNENFRNL · Accepted solution]

Roughly speaking, use GENERATEALL(vwUsers, FILTER(vwYears, ...)) to realize LEFT JOIN effect.

[EFont]

Indeed !!!

I played a bit with GenerateAll

Once I understood its power, I managed to do it from my sample's elements

The next step was to apply it to my solution and it works !

I also made a groupby on my calendar table to get only the years to simplify the results into a ctYears table, here is the resulting code

ctUserActivityAndYear = GENERATEALL(ctUserActivity,filter(ctYears,and(ctYears[Year] >= ctUserActivity[DateStarted].[Year], ctYears[Year] <= ctUserActivity[DateEnded].[Year])))

Here is the resulting table

CNENFRNL, I thank you very much ! 

Have an excellent weekend !

[EFont]

Just saw that the result table is not showing properly, here is an image instead:

[grantsamborn]

Hi @EFont 

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from 1) to 2).

I hope this helps.

[EFont]

Thank you for your help, grantsamborn !

I managed to do it with CNENFRNL's answer

Have an excellent weekend !