PowerBI LOOKUP/SEARCH formula similar to Microsoft Excel formula

[Ahollis]

Greetings, i am trying to formulate a DAX formula for my powerbi report via Transform Data (PowerQuery) than mimics or reproduces a LOOKUP/SEARCH formula i have in Excel: =IF([@[VENDOR_NAME]]=0,IFERROR(LOOKUP(4000,SEARCH(Output!$E$4:$E$717,AM314437),Output!$C$4:$C$717),""),[@[VENDOR_NAME]])
The formula above bascially searches for a vendor in a field called "Vendor Name", and returns the vendor name should one exist.  However, when a vendor is not found in the vendor name then it will seach for a vendor name in another field labled "Journal Entry Line Description', and if it finds a vendor name that matches the vendor name in a table then it returns the appropriate vendor name.  I am looking to create a formula that mimics the one above utilizing DAX.  Not sure if that can be done but if more information is required i would be happy to send it.

[Anonymous · Accepted solution]

Hi @Ahollis ,

Assume that you have the table 'Vendors' which include the field [VENDOR_NAME] and 'Output', you can create a calculated column as below in the table 'Vendors':

New_VendorName =
IF (
    ISBLANK ( 'Vendors'[VENDOR_NAME] ),
    MAXX (
        FILTER (
            Output,
            SEARCH ( Output[ColumnE], 'Vendors'[Journal Entry Line Description], 1, 0 ) > 0
        ),
        Output[ColumnE]
    ),
    [VENDOR_NAME]
)

Best Regards

[lbendlin]

in Power BI DAX you use COALESCE( .. ) for this.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...