Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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! |
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
CNENFRNL, I thank you very much !
Have an excellent weekend !
Just saw that the result table is not showing properly, here is an image instead:
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 !
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |