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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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