cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EFont
New Member

Logical clause for a join ?

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:

UserIDActivity
1010102010
1010102011
1010102012
1010102013
1010102014
1010102015
1010102016
2020202019
2020202020
2020202021
2020202022
2020202023
3030302021
3030302022
3030302023

 

Please help !

 

Thank you,

Erik

1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
CNENFRNL
Super User
Super User

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

EFont_0-1680303832440.png

 

CNENFRNL, I thank you very much ! 

Have an excellent weekend !

EFont
New Member

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

EFont_0-1680284251112.png

 

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.

Thank you for your help, grantsamborn !

I managed to do it with CNENFRNL's answer

 

Have an excellent weekend !

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors