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

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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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

 

 

  • RemiAnthonise's avatar
    RemiAnthonise
    Helper V
    7 years ago

    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 

  • RemiAnthonise's avatar
    RemiAnthonise
    Helper V
    7 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      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. 

      • RemiAnthonise's avatar
        RemiAnthonise
        Helper V
        7 years ago

        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!