To add a column in Power BI using if - else functions

[Sushant__08]

Dear All,

I am new to Power BI and was having issues in creating a new column.

Present table in Power BI is as below:

And I want to a new column named "EAC BQ" at the end as per the following rule.

If EACBQ Flag column has inputs ("Model" or "Forecast"), then EAC BQ column data will reflect to the column "Model BQ" and "Forecast BQ" respectively.

But if EACBQ Flag column is empty, then, EAC BQ will reflect to the BQs as per the status indicated. ( In case of above screenshot, only FR issued is seen. )

Could you please help to create a formula to accomodate the rule ?

Thanks in advance.

[Anonymous]

Hi @Sushant__08 ,

Thanks for your help, @rajendraongole1 .

Her's the calculated column with DAX:

EAC BQ = SWITCH([EACBQFlag],"",[Status],"Model","Model BQ","Forecast","Forecast BQ")

Or:

EAC BQ 2 = IF([EACBQFlag]=BLANK(),[Status],IF([EACBQFlag]="Model","Model BQ",IF([EACBQFlag]="Forecast","Forecast BQ")))

Best Regards,

Stephen Tao

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

[Sushant__08]

Thank you @Anonymous ,

But I was to assign numerical values in the new column "EAC BQ" from the available BQ data.

Like,

If "EACBQ flag" is blank, then my intention is to select a BQ data based on the status update. Like,

Status = Before Design Start ; Data = number / data in "Target BQ" column

Status = "Design Started" ; Data = Number in "Forecast BQ" column

Status = "FR Issued" ; Data = Number in "Model BQ" column

Status = "FC Issued" ; Data = Number in "Model BQ" column

But if EACBQ Flag column is not empty. If EACBQ Flag = "Model" then "EAC BQ" = number in "Model BQ" column.

Similarly,

If EACBQ Flag = "Forecast", then EAC BQ = Number in "Forecast BQ" column.

It is a bit confusing but could you please help me. I am stuck in this report from last few days.

Thanks so much for your attention.

[rajendraongole1]

Hi @Sushant__08 - create the new column EAC BQ based on the specified rules, you can use a combination of conditional logic in DAX.

Create calculated column as below:

EAC BQ =
VAR EACFlag = [EACBQ Flag]
RETURN
SWITCH(
TRUE(),
EACFlag = "Model", [Model BQ],
EACFlag = "Forecast", [Forecast BQ],
ISBLANK(EACFlag) && NOT(ISBLANK([FR issued])), [FR issued],
BLANK()
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Sushant__08]

Thank you for your reply.

But can we create a logic in DAX accomodating all conditions in status column ?

For instance,

In case of a blank EACBQFlag column, For status value,

Before Design Start ---- > To use column "Target BQ"

Design Started ---- > Forecast BQ

FR Issued ---- > Model BQ

FC Issued ---- > Model BQ

For now, only FR issued has been considered.

Thank you.

[Anonymous]

Hi Sushant,

The below calculation might meet your requirements.

NewColumn =

SWITCH(

    TRUE(),

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "Before Design Start", 'YourTable'[Target BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "Design Started", 'YourTable'[Forecast BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "FR Issued", 'YourTable'[Model BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "FC Issued", 'YourTable'[Model BQ],

    BLANK()

)

Regards,

Chiranjeevi Kudupudi

[Sushant__08]

Thank you @Anonymous sir,

The solution is almost okay.

But if "EACBQ Flag" column = "Model" or "Forecast", then we can directly refer to "Model BQ" and "Forecast BQ" columns.

And status info is not required to be considered.

Could you please assist in updating the code to include a scenerio that EACBQ Flag column in not blank. As, it could have either "Model" or "Forecast" as well.

[Anonymous · Accepted solution]

To accommodate the updated requirements, here’s the revised DAX formula. This formula checks both if the ‘EACBQFlag’ column is not blank and contains either "Model" or "Forecast" and assigns the respective BQ column value directly. If the ‘EACBQFlag’ column is blank, it uses the ‘status’ column to determine the value:

NewColumn =

SWITCH(

    TRUE(),

    'YourTable'[EACBQFlag] = "Model", 'YourTable'[Model BQ],

    'YourTable'[EACBQFlag] = "Forecast", 'YourTable'[Forecast BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "Before Design Start", 'YourTable'[Target BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "Design Started", 'YourTable'[Forecast BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "FR Issued", 'YourTable'[Model BQ],

    ISBLANK('YourTable'[EACBQFlag]) && 'YourTable'[status] = "FC Issued", 'YourTable'[Model BQ],

    BLANK()

)

Regards,

Chiranjeevi Kudupudi

[Sushant__08]

Thank you so much @Anonymous . It worked perfectly.