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.

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

 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
1 ACCEPTED SOLUTION
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]
)``````

2 REPLIES 2
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

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

## Helpful resources

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors