March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to 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.
Did I help you today? Please accept my solution and hit the Kudos button.
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])
Then you can create a table visual and get result you want.
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.
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.
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.
Hi @nfietz ,
If you add the calculated columns below to your table, it should give you what you need.
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.
Did I help you today? Please accept my solution and hit the Kudos button.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
125 | |
85 | |
69 | |
54 | |
45 |
User | Count |
---|---|
204 | |
106 | |
98 | |
65 | |
54 |