When I need to detect empty or missing values in a dataset, I use DAX functions in Power BI. In DAX, we have two functions, ISBLANK and ISEMPTY, and at first, they look like they do the same thing.
Both functions return TRUE when something is empty and FALSE when data exists. Because of this, many people get confused about when to use ISBLANK and when to use ISEMPTY.
In this tutorial, I will explain ISBLANK and ISEMPTY along with their syntax. I will show how to check whether a column value is missing using the ISBLANK DAX function, and how to check whether a table has no rows using the ISEMPTY function.
ISBLANK in Power BI DAX
The ISBLANK function in Power BI DAX is used to check whether a value is blank or missing. It works on single values, such as a column value, a calculated column, or a measure result.
If the value being checked is blank, ISBLANK returns TRUE. If the value contains any data, ISBLANK returns FALSE.
Syntax:
ISBLANK(<value>)Here, <value> can be:
- Any DAX expression that returns a single value
- A column value
- A measure
Check Column Value Blank Using ISBLANK() in Power BI
In this example, I will use a sample dataset named Products Ordered. This dataset contains order-related information such as:
Now, I want to create a new column that checks whether the Ship to Zip Code column is blank or not. The new column will return TRUE if the value is blank and FALSE if the value is not blank.
Follow the steps below:
- Open Power BI Desktop. Load the Products Ordered dataset.
- Go to Table View and select New Column from the Table tools tab.
- Enter the following DAX formula and click Enter:
Check Zip Code Blank = ISBLANK('Products Ordered'[Ship to Zip Code])
After this, a new column is added to the table. This column shows TRUE for rows where the Ship to Zip Code value is missing and FALSE where the value exists.
This way, you can check column value blank using ISBLANK() in Power BI.
Check Measure Result Blank Using ISBLANK() in Power BI
In this example, we will check whether a measure returns a blank value using the ISBLANK() function in Power BI.
Check the Excel below, which I will use in this example:
Follow the steps below:
- Open Power BI Desktop and load the Products Ordered dataset.
- Go to Table View and select the column. Click on New Measure from the Table tools tab.
- Enter the following DAX formula and press Enter:
Total Sales = SUM('Products Ordered'[Sales Amount])
- Click on New Measure. Enter the following DAX formula:
Check Measure Blank = ISBLANK([Total Sales])