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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.