Excel to dax

[Ashik008]

need help with converting excel to dax

=IF(IF(IF(VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,6,FALSE),VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE))=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:BK,63,FALSE),IF(VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,6,FALSE),VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)))="1. High","High",IF(IF(IF(VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,6,FALSE),VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE))=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:BK,63,FALSE),IF(VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)=0,VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,6,FALSE),VLOOKUP([@[Service ID]],'Vendor Services Report'!A:G,7,FALSE)))="2. Moderate","Moderate"

Table 1 and vendor service report has common id as "service id" column

column 6 -rating 1

column 7-Rating 2

Column 63 -old rating these are the name of the columns in the power bi .please help

[Anonymous · Accepted solution]

Hi @Moetazzahran ,thanks for your quick reply, I will add more.

Hi @Ashik008 ,

Here's what I think the table structure is. Are you adding a new calculated column to the 'Table'?

Try this.

Column = 
    IF(
        IF(
            IF(
                LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID]) = 0,
                LOOKUPVALUE('Vendor Services Report'[rating1],'Vendor Services Report'[Service ID],[Service ID]),
                LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID])
            ) = 0,
            LOOKUPVALUE('Vendor Services Report'[old rating],'Vendor Services Report'[Service ID],[Service ID]),
            IF(
                LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID]) = 0,
                LOOKUPVALUE('Vendor Services Report'[rating1],'Vendor Services Report'[Service ID],[Service ID]),
                LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID])
            )
        ) = "1.High",
        "High",
        IF(
            IF(
                IF(
                    LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID]) = 0,
                    LOOKUPVALUE('Vendor Services Report'[rating1],'Vendor Services Report'[Service ID],[Service ID]),
                    LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID])
                ) = 0,
                LOOKUPVALUE('Vendor Services Report'[old rating],'Vendor Services Report'[Service ID],[Service ID]),
                IF(
                    LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID]) = 0,
                    LOOKUPVALUE('Vendor Services Report'[rating1],'Vendor Services Report'[Service ID],[Service ID]),
                    LOOKUPVALUE('Vendor Services Report'[rating2],'Vendor Services Report'[Service ID],[Service ID])
                )
            ) = "2.Moderate",
            "Moderate",
            [else_value_if_false]
        )
    )

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

[Moetazzahran]

Hello @Ashik008 , 

Can you please add a dummy data sheet with the formula within it? It would great help recreate the formula in DAX. 

Thank you.