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

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.