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
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
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.

Top Solution Authors