DAX: current position > filter between 2 dates

[RemiAnthonise]

Hi,

I need help with the following issue:

We have a list with our employees. They work on a certain department (department = afdeling in my attached image below).

Currently I see for my testperson 2 departments (afdelingen). One department because of his previouw position / assignment in our company and one for the current position / assignment. 

We only want to see the current position. So it should be something like this:

Current position =

IF(positionworkerassignments2[start] <= TODAY() && positionworkerassignments2[end] >= TODAY(); departments[name]

But with this formula it gives me the error:

a single value cannot be determined, use min / max etc. You know it.

I guess it should be something like this:
Function =
MINX(PositionDetails;LOOKUPVALUE(PositionDetails[Function]; PositionDetails[PositionId];MAX(PositionWorkerAssignments[PositionID])))

I use this in a different measure. The problem is I always make mistakes with the lookupvalue so I don't get it working. Of should I use some other formula?

If the lookupvalue is right, can you tell me what I need according to the below image? For now it's a bit difficult to give you some sample data so if you can fix it with the above image, please!

Thanks, guys.

[Greg_Deckler · Accepted solution]

Huh, I hate saying this but, works for me. See attached, Page 1, Table3

[Greg_Deckler]

So, in theory, the MAX start date is the current position, so something like:

Measure = 
VAR __naam = MAX([Naam])
VAR __max = MAXX(FILTER(ALL('Table'),[Naam]=__naam),[Start])
RETURN IF(MAX([Start])=__max,"Current","Ended")

[RemiAnthonise]

Hi Greg @Greg_Deckler,

Thanks for your reply. I think we are almost there. See below my results. For 2 persons the outcome is right (see green lines), for 1 person it's wrong. Actually for more persons but I'm showing only these lines because this will explain enough.

When a person has more than 1 row (see 2 green lines below) the outcome is right. If a person has only 1 row (see red line below) it's always wrong. 

So, in summary, with your code:

if a person has 2 or more rows the outcome is right

if a person has only 1 row the outcome is wrong

Do you have any idea how to solve this? 

Thanks in advance.

[Greg_Deckler · Accepted solution]

Huh, I hate saying this but, works for me. See attached, Page 1, Table3

[RemiAnthonise]

Hi Greg @Greg_Deckler

Sorry to bother you with my problems (again) but if you have some time to check it out: this would be very nice.

Thanks a lot!

[Greg_Deckler · Accepted solution]

You were referencing the wrong table:

Current position = 

VAR __naam = MAX('Workers'[Name])
VAR __max = MAXX(FILTER(ALL('Positions'),[Name]=__naam),'Positions'[Start])
RETURN IF(MAX('Positions'[Start])=__max,"Current","Ended")

Attached. Also, your End column should really be a date field for this to work properly. Otherwise, as text, it is going to be wonky probably. 

[RemiAnthonise]

I think I've got it working. Thanks Greg @Greg_Deckler ,I'll mark your answer as solution.

If I'm still having troubles I'll let you know!

[RemiAnthonise]

Hi Greg @Greg_Deckler ,

Thanks for your sample data. I've reproduced some tables in your file, see below, and I still get the same error as I had before. I don't see how my sample data differs from yours (yours is also available in the sample file).

If we find out why this is different I can solve this in my own file as well.

Many thanks!

https://www.dropbox.com/s/ons07wrd4donhaj/sample.pbix?dl=0