Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Multiple IF Condition

Hi and good day to all,

Can someone help me on my new calculated column. I have a table with column scope, % materials, Budgeted hrs, Treade and Hour Equevalent. Basically what i want to achieved is 

  • if the Trade is equal to Scaff, then the Hour Equevalent is equal to Budgeted hrs
  • if the the Materials Column is Blank then the Hour Equevalent is equal to Budgeted hrs
  • if the Materials Column has value then the Hour Equevalent is equal to (Materials x Budgeted Hours) except for the Scaff Trade

Table:

Desired Output

Thank you

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi AllanBerces  - You can create the calculated column for "Hour Equivalent" in Power BI using DAX.

    Hour Equivalent =
    IF(
        Heoutput[Trade] = "Scaff",
        Heoutput[Budgeted hrs],
        IF(
            Heoutput[% Materials] = BLANK() || Heoutput[% Materials] = "Blank",
            Heoutput[Budgeted hrs],
            Heoutput[% Materials] * Heoutput[Budgeted hrs]
        )
    )

     

     

    Please check and i hope it works at your end.

     

     

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi AllanBerces  - You can create the calculated column for "Hour Equivalent" in Power BI using DAX.

    Hour Equivalent =
    IF(
        Heoutput[Trade] = "Scaff",
        Heoutput[Budgeted hrs],
        IF(
            Heoutput[% Materials] = BLANK() || Heoutput[% Materials] = "Blank",
            Heoutput[Budgeted hrs],
            Heoutput[% Materials] * Heoutput[Budgeted hrs]
        )
    )

     

     

    Please check and i hope it works at your end.

     

     

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi AllanBerces ,

     

    Your requirement can be easily handled with a calculated column in Power BI using the DAX formula below similar to the Excel example you produced:

    Hour Equivalent =
SWITCH(
    TRUE(),
    'YourTable'[Trade] = "Scaff", 'YourTable'[Budgeted hrs],
    ISBLANK('YourTable'[% Materials]), 'YourTable'[Budgeted hrs],
    'YourTable'[% Materials] * 'YourTable'[Budgeted hrs]
)

     

    Best regards,

  • divyed's avatar
    divyed
    Super User
    1 year ago

    Hello AllanBerces ,

     

    You can try below dax :

     

    Hour_Equivalent =
    IF(
        'table1'[Trade] = "Scaff",
        'table1'[Budgeted_hrs],
        IF(
            ISBLANK('table1'[Per_Material]),
            'table1'[Budgeted_hrs],
            'table1'[Per_Material] * 'table1'[Budgeted_hrs]
        )
    )

     

     

    Please modify table and field names or let us know in case you want differenyt result.

     

    Cheers