cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors