Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
LithanaM
Frequent Visitor

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
1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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

 

sevenhills_0-1646160609637.png

 

View solution in original post

2 REPLIES 2
LithanaM
Frequent Visitor

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

sevenhills
Super User
Super User

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

 

sevenhills_0-1646160609637.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.