Forum Discussion
Check for a Column with all null values
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?
6 Replies
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! 😊
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. 😊
