topic Re: Check for a Column with all null values in DAX Commands and Tips

Check for a Column with all null values

Hoping — Wed, 26 Jul 2023 08:20:38 GMT

I wat to check if an entire column has null values and if yes, add another column with value "YES NULL" else "NO NOT NULL" repeating in DAX.
ISBLANK does it for every row. But I want to check the entire column.

The check for nulls needs to happen after the filters are applied.

VAR TABLE1 = CALCULATETABLE( SUMMARIZE( TAB[Col1], TAB[Col2], TAB[Col3] ), KEEPFILTERS(FILTERTABLE) ) VAR NumberofRows = COUNTROWS(TABLE1) VAR NumberofNullRows = COUNTBLANK(TABLE1[Col3]) VAR Check = IF(NumberofRows = NumberofNullRows, "YES ALL NULL", "NO NOT NULL") VAR TABLE2 = ADDCOLUMNS(TABLE1, "Col4", Check) EVALUATE TABLE2

I think above code is failing beause Variable Check in the above code is a single value. How can I make it into a list so that it can be added to the column?

Re: Check for a Column with all null values

some_bih — Tue, 15 Aug 2023 12:39:25 GMT

Hi @Hoping in general to check if column contains null, one possible solution is

Is_Null_Column =
IF(
COUNTROWS(<YourTableName>) = COUNTBLANK(<YourTableName>[YourColumnName]),
"Yes:NULL",
"No:NOT NULL"
)

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Re: Check for a Column with all null values

Alef_Ricardo_ — Wed, 16 Aug 2023 01:19:07 GMT

You are correct that the `Check` variable in your code is a single value, and you cannot add a single value to a column. Instead, you can use the `Check` variable as an input to the `ADDCOLUMNS` function to create a new column with the desired values. Here's an example of how you can modify your code to achieve this:

```
VAR TABLE1 = CALCULATETABLE( SUMMARIZE( TAB[Col1], TAB[Col2], TAB[Col3] ), KEEPFILTERS(FILTERTABLE) )
VAR NumberofRows = COUNTROWS(TABLE1)
VAR NumberofNullRows = COUNTBLANK(TABLE1[Col3])
VAR Check = IF(NumberofRows = NumberofNullRows, "YES ALL NULL", "NO NOT NULL")
VAR TABLE2 = ADDCOLUMNS(TABLE1, "Col4", Check)
EVALUATE TABLE2
```

In this modified version of your code, the `Check` variable is used as an input to the `ADDCOLUMNS` function, which creates a new column `Col4` in `TABLE2` with the value of `Check` for each row. This should give you the desired result of a new column with the value `"YES ALL NULL"` if all values in `Col3` are null, and `"NO NOT NULL"` otherwise.

I hope this helps! Let me know if you have any further questions. 😊

Re: Check for a Column with all null values

Alef_Ricardo_ — Wed, 16 Aug 2023 01:19:35 GMT

Yes, that is one way to check if a column contains all null values. The `Is_Null_Column` measure you provided compares the number of rows in the table with the number of blank values in the specified column. If these two values are equal, it means that all values in the column are null, and the measure returns `"Yes:NULL"`. Otherwise, it returns `"No:NOT NULL"`.

Thank you for sharing your solution! 😊

Re: Check for a Column with all null values

some_bih — Wed, 16 Aug 2023 05:16:24 GMT

Hi @Hoping If this is solution, please accept it so other member of community could use it. Thank you

Re: Check for a Column with all null values

Hoping — Thu, 17 Aug 2023 01:13:58 GMT

@some_bih The check for NULL / BLANKS is after filtering. How can keep the filters in the above code ?

Re: Check for a Column with all null values

some_bih — Thu, 17 Aug 2023 06:19:11 GMT

Hi @Hoping the solution is for column, all rows in single column to be checked if there are NULL