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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
grggmrtn
Post Patron
Post Patron

Find first date in column based on another column - need it in M

I've created a custom column in DAX, which finds the first [Startdate] based on a column that is the [Service ID) (unfortunatly people have been inputting several start dates, bad data discipline - I need the first one to correct that error).

First Startdate = 
CALCULATE (
    MIN ( 'MyTable'[Startdate] );
    FILTER (
        'MyTable';
        'MyTable'[Service ID]
            = EARLIER ( 'MyTable'[Service ID] )
    )
)

But several of my columns in my query code (M) are based on [Startdate], so I would LOVE it if I could do this same thing in M.

 

Is it possible?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

it's possible in M, here you will find a little pbix file.

 

Basically create a Group By with two aggregations,

Use the column [Service ID] to group by

use two aggregations

  • use the Min Aggregation with your column Startdate
  • use the All Rows Aggregation with

Using All Rows creates a table preserving all columns and allows to expand the table in a later step. Just select all the grouped columns except ServiceID

A screenshot from the Group By dialog

image.png

 

After selecting OK, you can expand the table:

image.png

 

Selecting all the column that have been grouped:

image.png

Done 🙂

 

Hopefully this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey,

 

it's possible in M, here you will find a little pbix file.

 

Basically create a Group By with two aggregations,

Use the column [Service ID] to group by

use two aggregations

  • use the Min Aggregation with your column Startdate
  • use the All Rows Aggregation with

Using All Rows creates a table preserving all columns and allows to expand the table in a later step. Just select all the grouped columns except ServiceID

A screenshot from the Group By dialog

image.png

 

After selecting OK, you can expand the table:

image.png

 

Selecting all the column that have been grouped:

image.png

Done 🙂

 

Hopefully this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

That's beautiful @TomMartens - THANKS!!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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