Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors