cancel
Showing results for
Did you mean:
Member

## DAX: current position > filter between 2 dates

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: DAX: current position > filter between 2 dates

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

Proud to be a Datanaut!

Super User

## Re: DAX: current position > filter between 2 dates

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.

Proud to be a Datanaut!

7 REPLIES 7
Super User

## Re: DAX: current position > filter between 2 dates

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")

```

Proud to be a Datanaut!

Member

## Re: DAX: current position > filter between 2 dates

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?

Super User

## Re: DAX: current position > filter between 2 dates

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

Proud to be a Datanaut!

Member

## Re: DAX: current position > filter between 2 dates

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

Member

## Re: DAX: current position > filter between 2 dates

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!

Super User

## Re: DAX: current position > filter between 2 dates

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.

Proud to be a Datanaut!

Member

## Re: DAX: current position > filter between 2 dates

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!

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 26 members 921 guests
Recent signins: