Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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]
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |