Multiple IF Condition

[AllanBerces]

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

[AllanBerces]

Hi @rajendraongole1 @divyed @DataNinja777 thank you very much for the reply, I tried all your solution and it's perfectly. 

[divyed]

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

[DataNinja777]

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,

[rajendraongole1 · Accepted solution]

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.