Forum Discussion

LithanaM's avatar
LithanaM
Frequent Visitor
4 years ago
Solved

Conditional column in DAX using several lines

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

StudentProgramStart DateNumber of programsWhat I want
11111A20002Programme 1/2
11111B20042Programme 2/2
22222A20001Programme Unique
3333A20023Programme 1/3
3333B20053Programme 2/3
3333C20083Programme 3/3
4444D20081Programme Unique
Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago

    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]
            )

     

     

2 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    4 years ago

    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]
            )

     

     

  • LithanaM's avatar
    LithanaM
    Frequent Visitor
    4 years ago

    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