DAX equvalent of Excel 'ISNA' formula?

[yaz_d89]

Hi,

Does the ISNA function held in Excel have an equvalent in Power Bi Desktop? Have tried ISERROR, and various others, however, can't find one that works. I have a data source in Excel which I'm trying to move to Power BI, however, a lookup based on ISNA can't be replicated in the same way.

Thanks,

[v-ljerr-msft · Accepted solution]

Hi @yaz_d89,

In Excel, the #N/A error is returned when the lookup functions can't find a referenced value. In Dax, a BLANK is returned instead of #N/A error when there is no match that satisfies all the search values in LOOKUPVALUE Function (DAX). In this scenario, ISBLANK Function (DAX) should be equivalent of ISNA function in Excel.

In addition, there is another difference between lookup functions(VLOOKUP, HLOOKUP, LOOKUP, or MATCH) in Excel and LOOKUPVALUE Function (DAX): If several rows in your table match your criteria, then lookup functions in Excel will only return the first (or last) match. On the contrary, LOOKUPVALUE in DAX may return an error if several rows match your criteria. In this scenario, you may need to use TOPN Function (DAX)  to mimic the behaviour of lookup functions in Excel.

Reference: https://thedataspecialist.wordpress.com/tag/lookup-in-dax/

Regards

[Greg_Deckler]

MyColumn = IF([Column]="#N\A",1,0)

?