Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |