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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AshDil
Helper V
Helper V

Want to show only current Vice president of Employee

Hi,

 

I have Employee hierarchy data as follows,

Vice-PresidentDirectorManagerEmployeedate
AdamJackFernandezSteve20/11/2019
AliaSamsonMikkySteve13/12/2019
BenWilliamMikkySteve15/2/2020
StokesJackMikkySteve27/03/2020
WindyWilliamFernandezSteve15/07/2020
CarlSamsonFernandezSteve20/12/2020
MarxWilliamMikkySteve1/1/2021
ShellySamsonFernandezSteve12/4/2021
WiongJackMikkySteve8/8/2021

The Date column is the day when a person has becom Vice president of Steve.
I have a employee named Steve for whom the Vice president changes with time. I want to show the latest Vice president of Steve i.e., Wiong.
I tried to calculate max date to get current Vice-president for Steve,

 

dummy =
var _latestdate = MAXX(ALLSELECTED('table'),'table'[Date])
return
_latestdate

 

but when I'm dragging Vice-President column and Employee column to visual I'm getting all Vice-Presidents of Steve and dates.

 

Please help me to do it.

 

Thanks,

AshDil.

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @AshDil ,

 

If you want to show current Vice president based on the lastest Date for each employee ,you could try:

Measure= 
var _date=MAXX(FILTER(ALL('Table'),[Employee]=MAX('Table'[Employee])),[date])
return CALCULATE(MAX('Table'[Vice-President]),ALLEXCEPT('Table','Table'[Employee]),'Table'[date]=_date)

Eyelyn9_1-1634780448704.png

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

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @AshDil ,

 

If you want to show current Vice president based on the lastest Date for each employee ,you could try:

Measure= 
var _date=MAXX(FILTER(ALL('Table'),[Employee]=MAX('Table'[Employee])),[date])
return CALCULATE(MAX('Table'[Vice-President]),ALLEXCEPT('Table','Table'[Employee]),'Table'[date]=_date)

Eyelyn9_1-1634780448704.png

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

Samarth_18
Community Champion
Community Champion

Sure @AshDil Let me know if its working or you find anyother difficulty.

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Samarth_18
Community Champion
Community Champion

Hi @AshDil 

 

You can use vice president column with Last and Date with latest like below

 

Samarth_18_1-1634555517263.png

 

 

image.png

 

Thanks,

Samarth

 

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Hi @Samarth_18 ,

Thanks for sharing. I'll work on it.

Thanks,

AshDil.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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