Forum Discussion

Ashik008's avatar
Ashik008
Frequent Visitor
2 years ago
Solved

Excel to dax

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Moetazzahran's avatar
    Moetazzahran
    Icon for Resolver II rankResolver II
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.