Forum Discussion
To add a column in Power BI using if - else functions
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.
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
8 Replies
- rajendraongole1
Super User
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!!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.
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
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.
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.
