topic Re: Convert Excel formula to DAX in DAX Commands and Tips

Convert Excel formula to DAX

ExceLover — Tue, 28 Nov 2023 11:21:55 GMT

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

Re: Convert Excel formula to DAX

123abc — Tue, 28 Nov 2023 12:05:37 GMT

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.

Re: Convert Excel formula to DAX

ExceLover — Tue, 28 Nov 2023 14:02:04 GMT

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.

Re: Convert Excel formula to DAX

123abc — Tue, 28 Nov 2023 13:09:17 GMT

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.

Re: Convert Excel formula to DAX

FreemanZ — Tue, 28 Nov 2023 13:29:09 GMT

hi ExceLover ,

try like:

column =

SWITCH(

TRUE(),

ISBLANK([C2Column]), 0,

ISBLANK([A2Column]), 0,

)

Re: Convert Excel formula to DAX

ExceLover — Tue, 28 Nov 2023 14:00:21 GMT

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.

Re: Convert Excel formula to DAX

ExceLover — Tue, 28 Nov 2023 16:08:15 GMT

Hi FreemanZ

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

Thanks for your help!

Re: Convert Excel formula to DAX

ExceLover — Wed, 29 Nov 2023 16:02:54 GMT

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?

Re: Convert Excel formula to DAX

Anonymous — Sat, 16 Nov 2024 08:51:19 GMT

hi@Excelover

ry like:

column =

SWITCH(

TRUE(),

ISBLANK([C2Column]), 0,

ISBLANK([A2Column]), 0,

)