Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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!
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.
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |