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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Deevo_
Resolver I
Resolver I

Custom Column to retrieve the very first iteration of a Project name

Hi All,

Thanks in advance for your time.

What I am trying to do:

  • I have a project which has gone through multiple name changes, but i need to retrieve the very first name that the project was given.
  • I have tried to use MIN to calculate the earliest ID number, and then output the Project Name that is related to that ID number, but I am unable to repurpose other similar scenarios on this forum.

Sample Data:

IDProject Name
001Cloud enhancements
002Cloud enhancements version 2
003

Cloud enhancements version 3

 

Expected result:

When i display the result in a table, I expect to see the project name "Cloud enhancements". This is the original name of the project.

 

thank you.

1 ACCEPTED SOLUTION
Mahesh0016
Super User
Super User

@Deevo_ I hope this hepls you!THANK YOU!!

 

ProjectName =
MAXX (
    FILTER (
        'Table (2)',
        'Table (2)'[ID] = 001
            && CONTAINSSTRING ( EARLIER ( 'Table (2)'[Project Name] ), 'Table (2)'[Project Name] )
    ),
  'Table (2)'[Project Name]
)
Mahesh0016_0-1686206762952.png

 

View solution in original post

3 REPLIES 3
Deevo_
Resolver I
Resolver I

Thank you so much, this worked a treat. I modified your DAX slightly to pick the Earliest ID number instead of manually typing it in as I have multiple different projects.

I replaced "  'Table (2)'[ID] = 001 " with "MIN([ID])".

You have made my day !!!! Thanks once again

Mahesh0016
Super User
Super User

@Deevo_ I hope this hepls you!THANK YOU!!

 

ProjectName =
MAXX (
    FILTER (
        'Table (2)',
        'Table (2)'[ID] = 001
            && CONTAINSSTRING ( EARLIER ( 'Table (2)'[Project Name] ), 'Table (2)'[Project Name] )
    ),
  'Table (2)'[Project Name]
)
Mahesh0016_0-1686206762952.png

 

Hi Mahesh0016,

I have come to realise that replacing 'Table (2)'[ID] = 001 " with "MIN([ID])" does not work. I believe its because I have not specified in the DAX how to group the projects together. The piece of data missing from my Example is the "Project Code (Level 1 & ChangeID)" and the "Project Code Level 1".

The updated Sample date looks like this:

Sample Data:

Project Code (Level 1 & ChangeID)Project Code Level 1ChangeIDProject NameExpected Result
P-1234-001P-1234001Cloud enhancementsCloud enhancements
P-1234-002P-1234002Cloud enhancements version 2Cloud enhancements
P-1234-003P-1234003

Cloud enhancements version 3

Cloud enhancements

P-4321-001

P-4321001Node Upgrade

Node Upgrade

P-4321-002P-4321002Node Upgrade version 2

Node Upgrade

P-4321-003P-4321003

Node Upgrade version 3

Node Upgrade

Question:

Can you please help me to revise your DAX solution to Group by the "Project Code level 1" and then choose the "Project Name" of the earliest "ChangeID"?

 

Expected result:

  1. P-1234 Project Name = Cloud Enhancements
  2. P-4321 Project Name = Node Upgrade

Many thanks

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors