Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Vlookup Conversion to DAX

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

LikhithaVG123_0-1663842473195.png

Any help please?

6 REPLIES 6
v-chenwuz-msft
Community Support
Community Support

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
Not applicable

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

LikhithaVG123_0-1664178700455.png


Thanks

Anonymous
Not applicable

Hi,

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

Anonymous
Not applicable

Hi,

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

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 NumberDateTimeUnits
19/20/20228am1
19/20/202210am1
19/20/20221pm1
19/20/20224pm1
19/21/20227am1
19/21/20229am1
19/21/202211am1
19/21/20221pm1
19/22/20226am1
19/22/202210am1
19/22/20222pm1
29/20/20228am1
29/20/202210am1
29/20/20221pm1
29/20/20224pm1
29/21/20227am1
29/21/20229am1
29/21/202211am1
29/21/20221pm1
29/22/20226am1
39/21/202210am1
39/21/20222pm1
39/22/20229am1
39/22/202211am1
39/22/20221pm1

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors