LOOKUPVALUE return min/max of values found

[Noivilbo]

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.

I've got the code below, but this retursn all rows, instead of the earliest date within that. 

Earliest_Date_Next_Stage = LOOKUPVALUE(Sheet1[Date];Sheet1[Candidate ID];Sheet1[Candidate ID];Sheet1[Stage];Sheet1[Stage]+1)

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!

[v-cherch-msft · Accepted 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,

[Noivilbo]

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!

[SebOléOlé]

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!

[Anonymous]

Can you load some sample data and a quick mock-up of what you would like to see?

[Noivilbo]

Sure - please see the example below:

https://www.dropbox.com/sh/mjwb4ka3vypo8zb/AACBUKlyvEyZ6atYUdFhhl0ya?dl=0

[Noivilbo]

Any ideas, anyone? 

Is my question formulated too complex? or is this not as easy to implement in power BI?

Thx