Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Helper V
7 years ago
Solved

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.

Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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. 

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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's avatar
      RemiAnthonise
      Helper V
      7 years ago

      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.