cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

position.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: DAX: current position > filter between 2 dates

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


7 REPLIES 7
Super User
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")


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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? 

Thanks in advance.

position2.jpg

Super User
Super User

Re: DAX: current position > filter between 2 dates

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 26 members 921 guests
Please welcome our newest community members: