Vlookup Conversion to DAX

[Anonymous]

Hi,

I need help in converting Vlookup  as = +IF(ISERROR(VLOOKUP(AI3,$AI$1:AI2,1,0))=FALSE,0,1) to Dax Formula

 AI Column Data as below

Any help please?

[v-chenwuz-msft]

Hi @Anonymous ,

Are you trying to find out if the current value has appeared before.

If yes, please create an index column in power query editor first and use the code as the following:

column =
IF (
    COUNTROWS (
        FILTER (
            ALL ( table ),
            [index] < EARLIER ( 'table'[index] )
                && [number] = EARLIER ( 'table'[number] )
        )
    ) > 0,
    1,
    0
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for the solution,
i have tried using the formula which you shared but i am facing the loading loading from 30mins. Please see the screenshot attached 
The source i have connected is excel count of records is approximately  31K. Any suggestions please

Thanks

[Anonymous]

Hi,

Any update please on the vlookup - +IF(ISERROR(VLOOKUP(AI3,$AI$1:AI2,1,0))=FALSE,0,1) to Dax Formula

[Anonymous]

Hi,

Any help on this Vlook up to Dax conversion - +IF(ISERROR(VLOOKUP(AI3,$AI$1:AI2,1,0))=FALSE,0,1)

[Greg_Deckler]

@Anonymous You can use LOOKUPVALUE or MAXX(FILTER(...),...) for VLOOKUP replacement.

[KellerB]

Hi! I have a problem that I have been trying to solve for days. I need to calculate working hours by manufacturing line over multiple days. the problem is I have to do this by time of first unit produced-time of last unit produced for each day. I dont know how to go through each day, take the min time and max time, subtract the two, and add to a running total going through everyday that my date slicer has included. If you are able to help that would be awesome!! Let me know 🙂 

Here is some sample data. This will eventually allow me to calculate an accurate "Units per hour".

Line Number	Date	Time	Units
1	9/20/2022	8am	1
1	9/20/2022	10am	1
1	9/20/2022	1pm	1
1	9/20/2022	4pm	1
1	9/21/2022	7am	1
1	9/21/2022	9am	1
1	9/21/2022	11am	1
1	9/21/2022	1pm	1
1	9/22/2022	6am	1
1	9/22/2022	10am	1
1	9/22/2022	2pm	1
2	9/20/2022	8am	1
2	9/20/2022	10am	1
2	9/20/2022	1pm	1
2	9/20/2022	4pm	1
2	9/21/2022	7am	1
2	9/21/2022	9am	1
2	9/21/2022	11am	1
2	9/21/2022	1pm	1
2	9/22/2022	6am	1
3	9/21/2022	10am	1
3	9/21/2022	2pm	1
3	9/22/2022	9am	1
3	9/22/2022	11am	1
3	9/22/2022	1pm	1