Convert Excel formula to DAX

[ExceLover]

Hello,

I am trying to create a new column in Power BI named PEN where each row in this column is a calculation based on values in three other columns.

The formula in Excel is as below, but I cannot figure out the equivalent in DAX to make this work, which I think might be because I am using a calculated column rather than an aggregated value.

=IF(ISBLANK(B2),IF(ISBLANK(C2),IF(ISBLANK(A2),0,1),0),0)

The Power BI table name is “Projects” and the Excel to Power BI column name equivalents are as below:

Column A – TLS

Column B – ULS

Column C - DCP

Please could anyone suggest what formula I would use in DAX to make this work?

Thanks

[FreemanZ · Accepted solution]

hi @ExceLover ,

try like:

column = 

SWITCH(

    TRUE(),

    ISBLANK([C2Column]), 0,

    ISBLANK([A2Column]), 0,

    1

)

[Anonymous]

hi@Excelover

ry like:

column = 

SWITCH(

    TRUE(),

    ISBLANK([C2Column]), 0,

    ISBLANK([A2Column]), 0,

    1

)

[ExceLover]

I have a different scenario now, I need 3 new columns that contain either 1 or 0 in the rows, based on the values in the rows in the columns DCP, ULS and TLS.

The pseudocode is:

New column 1 =

If DCP is >=0 then value should be 0, otherwise;

               If ULS is >=0 then value should be 0, otherwise;

                              If TLS is >=0 then value should be 1, otherwise should be 0

New column 2 =

If DCP is >=0 then value should be 0, otherwise;

               If ULS is >=0 then value should be 0, otherwise;

                              If TLS is >=0 then value should be 1, otherwise should be 0

New column 3 =

If DCP is >=0 then value should be 1, otherwise;

               If ULS is >=0 then value should be 0, otherwise;

                              If TLS is >=0 then value should be 0, otherwise should be 0

Please could you suggest the DAX formulae I should use for the 3 new columns?

[ExceLover]

Hi @FreemanZ 

Thank you for the suggestion, this works as it should.

Thanks for your help!

[123abc]

Certainly! In Power BI, you can create a calculated column using DAX with a formula similar to your Excel formula. The DAX equivalent would be:

PEN =
IF (
ISBLANK ( Projects[TLS] ),
IF (
ISBLANK ( Projects[ULS] ),
IF ( ISBLANK ( Projects[DCP] ), 0, 1 ),
0
),
0
)

This DAX formula checks if TLS is blank. If it is, it checks if ULS is blank. If ULS is also blank, it checks if DCP is blank. If DCP is blank, it returns 0; otherwise, it returns 1. If ULS is not blank, it returns 0. If TLS is not blank, it returns 0.

Make sure to replace "Projects" with your actual table name.

You can add this formula as a new calculated column in your "Projects" table in Power BI, and it should provide the same result as your Excel formula.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[ExceLover]

Hello,

This doesn't work, it generates the error below.

A single value for column 'DCP' in table 'Projects' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

[123abc]

likely due to the fact that it's trying to compare columns directly, which can be problematic in certain contexts. Let's modify the formula to address this issue:

PEN =
IF (
ISBLANK ( 'Projects'[ULS] ) || ISBLANK ( 'Projects'[DCP] ) || ISBLANK ( 'Projects'[TLS] ),
0,
1
)

This revised formula checks if any of the columns 'ULS', 'DCP', or 'TLS' is blank. If any of them is blank, it returns 0; otherwise, it returns 1. This simplification should help avoid the error you encountered. Please replace 'Projects' with the actual name of your table.

If you continue to experience issues, it may be helpful to provide more details about your data model or any additional requirements you have for the calculation.

[ExceLover]

This still doesn't work, I receive the error below:

A single value for column 'ULS' in table 'Projects' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.