Forum Discussion
DAX: current position > filter between 2 dates
Huh, I hate saying this but, works for me. See attached, Page 1, Table3
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.
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")
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.
Huh, I hate saying this but, works for me. See attached, Page 1, Table3
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!
