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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors