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
Dear community!
I am always amazed by the support we can find there. Please let me first thank you all for your dedication in helping the beginners like me!
I am trying to add a conditional column in DAX that will tell me in which order a students did his programs.
Is it possible t o write a formula directly in DAX that will take into account the year start of the program for each student in order to add a column that will tell me in what orders they did their program?.
What I would like to manage is to get something like what is written below: :
If Number of programs is 1 : - Programme Unique (when total number of program is one)
If Number of programs is 2 -Program 1/2 for the first start date, -Program 2/2 for the last date
If Number of programs is 3: Program 1/3 for the first start date, Program 2/3 for the second start date, -Program 3/3 for the last date
If Number of programs is 4: Program 1/4 for the first start date, Program 2/4 for the second start date,...
etc...(see table below)
Is it something feasible or am I asking too much from DAX?
Thank you for your answer,
Nathalie
Student | Program | Start Date | Number of programs | What I want |
11111 | A | 2000 | 2 | Programme 1/2 |
11111 | B | 2004 | 2 | Programme 2/2 |
22222 | A | 2000 | 1 | Programme Unique |
3333 | A | 2002 | 3 | Programme 1/3 |
3333 | B | 2005 | 3 | Programme 2/3 |
3333 | C | 2008 | 3 | Programme 3/3 |
4444 | D | 2008 | 1 | Programme Unique |
Solved! Go to Solution.
Add a column and try this
Column 1 = "Programme " &
if( 'Table'[Number of programs] = 1, " Unique",
RANKX(
filter('Table', 'Table'[Student] = EARLIER('Table'[Student]))
, 'Table'[Start Date],, asc
) & "/ " & 'Table'[Number of programs]
)
Amazing! Thanks a lot! I will look more into the new knowledge on RankX and EARLIER to better use it next time!
Have a great day!
Nathalie
Add a column and try this
Column 1 = "Programme " &
if( 'Table'[Number of programs] = 1, " Unique",
RANKX(
filter('Table', 'Table'[Student] = EARLIER('Table'[Student]))
, 'Table'[Start Date],, asc
) & "/ " & 'Table'[Number of programs]
)
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 |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
125 | |
108 | |
60 | |
55 |