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
amty63
Helper III
Helper III

Need help to find managers according to Min start date key and Max end date key

Hello,
Below is the dataset :

EMP_IDEmployee NameStart Date keyEnd Date keyEmp Manager Name
999999ABC2016032120160930Mgr1
999999ABC2016100120161002Mgr2
999999ABC2016100320161106Mgr2
999999ABC2016110720170501Mgr2
999999ABC2017050220170522Mgr2
999999ABC2017052320170728Mgr3
999999ABC2017072920170730Mgr3
999999ABC2017073120171205Mgr3
999999ABC2017120620180129Mgr3
999999ABC2018013020180630Mgr3
999999ABC2018070120180902Mgr3
999999ABC2018090320190301Mgr4
999999ABC2019030220190915Mgr4
999999ABC2019091620191022Mgr4
999999ABC2019102320191023Mgr1
999999ABC2019102420191031Mgr1
999999ABC2019110120191102Mgr1
999999ABC2019110320211010Mgr1
999999ABC2021101120211231Mgr1


I am trying to use below DAX but it is not working :

SUMMARIZE(DIM_EMP,DIM_EMP[EMP_ID],DIM_EMP[Employee Name],DIM_EMP[Emp Manager Name],"ST_Date",MIN(DIM_EMP[START DATE key]),"EN_Date",MAX(DIM_EMP[END DATE key]))



desired result is :

EMP_IDEmployee NameStart Date keyEnd Date keyEmp Manager Name
999999ABC2016032120160930Mgr1
999999ABC2016100120170522Mgr2
999999ABC2017052320180902Mgr3
999999ABC2018090320191022Mgr4
999999ABC2019102320211231Mgr1
4 REPLIES 4
amty63
Helper III
Helper III

please help

v-zhangti
Community Support
Community Support

Hi, @amty63 

 

You need to add an index column to Power Query first. 

Column:

MIN = CALCULATE(MIN('Table'[Start Date key]),FILTER('Table',[Emp Manager Name]=EARLIER('Table'[Emp Manager Name])))
Date Min = 
var _1=CALCULATE(MAX('Table'[Emp Manager Name]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
var _2=IF([Emp Manager Name]=_1,[MIN],[Start Date key])
return
IF([Start Date key]=_2&&_1=BLANK(),_2,IF([Start Date key]=_2,[MIN],BLANK()))
MAX = CALCULATE(Max('Table'[End Date key]),FILTER('Table',[Emp Manager Name]=EARLIER('Table'[Emp Manager Name])))
Date Max = 
var _1=CALCULATE(MAX('Table'[Emp Manager Name]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
var _2=IF([Emp Manager Name]=_1,[MAX],[End Date key])
return
IF([End Date key]=_2,_2,BLANK())

vzhangti_0-1671609549472.png

Table:

Table 2 = 
Var _table=SUMMARIZE('Table','Table'[EMP_ID],'Table'[Employee Name],'Table'[Emp Manager Name],'Table'[Date Min],'Table'[Date Max])
Return
FILTER(_table,[Date Max]<>BLANK())

vzhangti_1-1671609600728.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

@v-zhangti 
Thanks for the reply,
However, I noticed your solution's last row is reflecting 20211011, it should come 20191023 as per requirement, kindly help.

amty63
Helper III
Helper III

Kindly help

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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.