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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
nfietz
Helper I
Helper I

Returning data with earliest and latest date based on 2 columns (M/Power Query Editor)

Hi,

 

***Update: Thanks to all for the solutions. I'm new to PBI and realized based on the solutions I should have specified that I'm looking to do this via M/Power Query Editor. This will create a table that I can then join to other fact tables.

 

I'm trying to pare down an employee table that currently has multiple lines per Employee/Sup combination to a single line per Employee/Sup combination. The desired result would have the earliest Effective Start and latest Effective Stop per Employee/Sup combination. Below is an example of the input and desired output.

 

I'm not exactly sure what terminology to use to search for a solution, so even if you don't have a solution but can provide me with some key search terms I'd appreciate it. 

 

Thanks.

 

2022-08-22_11-59-29.png

 

1 ACCEPTED SOLUTION

Hi @nfietz ,

 

Even easier to do on PQ. Go to transform ribbon>Group by.

 

Group by your dimsension and use Min/Max aggregation to get your dates.

 

davehus_0-1661435569301.png

Did I help you today? Please accept my solution and hit the Kudos button.

View solution in original post

8 REPLIES 8
v-yadongf-msft
Community Support
Community Support

Hi @nfietz ,

 

Please try following DAX to create new columns:

 

Earliest Effective start = MINX(FILTER('table','table'[Supervisor name]=EARLIER('table'[Supervisor name])),'table'[Effective start])

Latest Effective Stop = MAXX(FILTER('table','table'[Supervisor name] = EARLIER('table'[Supervisor name])),'table'[Effective Stop])

 

 

vyadongfmsft_0-1661420050401.png

 

Then you can create a table visual and get result you want.

vyadongfmsft_1-1661420050404.png

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yadong,

 

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

Ashish_Mathur
Super User
Super User

Hi,

To your visual, drag Employee Name, Employee ID and Supervisor Name.  Write these measures

Measure1 = min(Data[Effective start])

Measure2 = max(Data[Effective stop])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

davehus
Memorable Member
Memorable Member

Hi @nfietz ,

If you add the calculated columns below to your table, it should give you what you need.

 

First Date = CALCULATE(MIN('Table'[Effective Date]), ALLEXCEPT('Table','Table'[Supervisor Name],'Table'[Employee Name]))
 
Last Date = CALCULATE(MAX('Table'[Effective Stop Date]), ALLEXCEPT('Table','Table'[Supervisor Name],'Table'[Employee Name]))
 
Hope this helps.
 
Did I help you today? Please accept my solution and hit the Kudos button.

Hi Dave,

 

Thanks for providing this solution. Based on your responses and others, I realized that I failed to specify that I'd like to do this within the Power Query editor/M language. The reason is that I actually need to build this table that can be joined to other fact tables. 

Hi @nfietz ,

 

Even easier to do on PQ. Go to transform ribbon>Group by.

 

Group by your dimsension and use Min/Max aggregation to get your dates.

 

davehus_0-1661435569301.png

Did I help you today? Please accept my solution and hit the Kudos button.

Thank you @davehus!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.