Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]
)
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |