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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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