topic Re: Vlookup Conversion to DAX in DAX Commands and Tips

Vlookup Conversion to DAX

Anonymous — Thu, 22 Sep 2022 10:29:24 GMT

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?

Re: Vlookup Conversion to DAX

Greg_Deckler — Thu, 22 Sep 2022 10:51:29 GMT

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

Re: Vlookup Conversion to DAX

Anonymous — Thu, 22 Sep 2022 11:34:13 GMT

Hi,

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

Re: Vlookup Conversion to DAX

KellerB — Thu, 22 Sep 2022 14:46:12 GMT

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

Re: Vlookup Conversion to DAX

Anonymous — Fri, 23 Sep 2022 07:03:50 GMT

Hi,

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

Re: Vlookup Conversion to DAX

v-chenwuz-msft — Mon, 26 Sep 2022 07:36:13 GMT

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.

Re: Vlookup Conversion to DAX

Anonymous — Mon, 26 Sep 2022 07:52:05 GMT

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