Forum Discussion

ExceLover's avatar
ExceLover
Frequent Visitor
2 years ago
Solved

Convert Excel formula to DAX

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 ...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi ExceLover ,

     

    try like:

    column = 

    SWITCH(
        TRUE(),
        ISBLANK([C2Column]), 0,
        ISBLANK([A2Column]), 0,
        1
    )

123abc's avatar
123abc
Community Champion
2 years ago

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's avatar
ExceLover
Frequent Visitor
2 years ago

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's avatar
    123abc
    Community Champion
    2 years ago

    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's avatar
      ExceLover
      Frequent Visitor
      2 years ago

      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.