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'm trying to do a LOOKUPVALUE formula, but I'd need to return only the lowest value.
But I can't get it to work (normal LOOKUPVALUE returns errors, given that it matches multiple values)
Background
I'm working with some HR data, and I've got a data table that shows all interactions with potential candidates. Based on this, I'd like to show the average time that candidates spend per stage.
In the example below:
- The stage column shows the sequential stages that a candidate must pass;
- The status column shows me the current status.
I'd like to come to the result in the orange column (and then take an average of non-blank values of that column), and I'd try to get there by building the grey columns first.
(obviously - I'm open to feedback as well if there is an easier method to build my graph)
Question: I would like to calculate earliest date of the next stage per candidate (if any) in Dax.
Any ideas?
(I'm aware I could do this in the backend by copying the table, doing "group by" and retaining only the rows I need, and then merging that info back to the initial file. but that seems barbaric overkill, and would significantly complicate the backend I'd say)
Thanks!
Solved! Go to Solution.
Hi @Noivilbo
You may create a column with below formula.
Column = VAR next_stage = CALCULATE ( MIN ( Sheet1[Date] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Candidate ID] ), Sheet1[Stage] = EARLIER ( Sheet1[Stage] ) + 1 ) ) VAR max_date = CALCULATE ( MAX ( Sheet1[Date] ), ALLEXCEPT ( Sheet1, Sheet1[Candidate ID], Sheet1[Stage] ) ) VAR min_date = CALCULATE ( MIN ( Sheet1[Date] ), ALLEXCEPT ( Sheet1, Sheet1[Candidate ID], Sheet1[Stage] ) ) RETURN IF ( Sheet1[Date] = max_date, IF ( NOT ( ISBLANK ( next_stage ) ), DATEDIFF ( min_date, next_stage, DAY ), DATEDIFF ( min_date, max_date, DAY ) ) )
Regards,
Hi @Noivilbo
You may create a column with below formula.
Column = VAR next_stage = CALCULATE ( MIN ( Sheet1[Date] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Candidate ID] ), Sheet1[Stage] = EARLIER ( Sheet1[Stage] ) + 1 ) ) VAR max_date = CALCULATE ( MAX ( Sheet1[Date] ), ALLEXCEPT ( Sheet1, Sheet1[Candidate ID], Sheet1[Stage] ) ) VAR min_date = CALCULATE ( MIN ( Sheet1[Date] ), ALLEXCEPT ( Sheet1, Sheet1[Candidate ID], Sheet1[Stage] ) ) RETURN IF ( Sheet1[Date] = max_date, IF ( NOT ( ISBLANK ( next_stage ) ), DATEDIFF ( min_date, next_stage, DAY ), DATEDIFF ( min_date, max_date, DAY ) ) )
Regards,
Hi!
Sorry for my late response, I didn't get an email this time that I had a reply on this topic;
Was already prepared to accept that I wouldn't get a response.
But your solution is perfect, even with variables to avoid a load of extra columns.
Thanks!
Hi Noivilbo,
I have a similar issue where the lookupvalue formula matches multiple values.
Does anyone have more experience how to solve this?
Thanks a lot!
Can you load some sample data and a quick mock-up of what you would like to see?
Sure - please see the example below:
https://www.dropbox.com/sh/mjwb4ka3vypo8zb/AACBUKlyvEyZ6atYUdFhhl0ya?dl=0
Any ideas, anyone?
Is my question formulated too complex? or is this not as easy to implement in power BI?
Thx
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |