cancel
Showing results for
Did you mean:
Frequent Visitor

## Identify Blank Cells in RELATED column

Hi Everyone,

I created a RELATED column on the DATA section of my report to return a value matching my column. As it was expected, the column contains Blank values as well as zero values as Decimal (e.g. 0.00). I would like to create another column that compares the new column with an existing column. But I'm having trouble returning a value where there is a blank cell.

Example:

Column # | Value 1 | Value 2 | Desired Third Column

1        | 0.50      | 1.25      | Value 2 = "Higher"

2        | 1.50      | 0.554    | Value 2 = "Lower"

3        | 0.00      | 0.00      | Value 2 = "Same"

4        | 0.00      |              | Value 2 = "Not Available"

The problem I'm having is that it appears the blanks are generating the same values as a 0.00 - So the IF formula I created does not recognize blanks.

Here is the formula syntax I'm using:

KPIValueFY18 = IF( Value 2 = Value 1, "Same", Value 2 = [BlankValue], "N/A", IF Value 2 > Value 1, "Higher", IF(Value 2 < Value 1,"Lower"))))

I appreciate the help!
1 ACCEPTED SOLUTION
Solution Sage

Hi @MarvinGallo ,

We can use the ISBLANK() function to judge if it is a blank value, please refer to the following DAX query:

```KPIValueFY18 =
IF (
ISBLANK ( Table1[Value2] ),
"N/A",
IF (
Table1[Value1] = Table1[Value2],
"Same",
IF ( Table1[Value1] < Table1[Value2], "Higher", "Lower" )
)
)```

The result will like below:

Best Regards,

Teige

Solution Sage

Hi @MarvinGallo ,

We can use the ISBLANK() function to judge if it is a blank value, please refer to the following DAX query:

```KPIValueFY18 =
IF (
ISBLANK ( Table1[Value2] ),
"N/A",
IF (
Table1[Value1] = Table1[Value2],
"Same",
IF ( Table1[Value1] < Table1[Value2], "Higher", "Lower" )
)
)```

The result will like below:

Best Regards,

Teige

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors