BLANK

BLANK

How to Use the Power BI DAX function BLANK

Syntax of the BLANK Function

The basic syntax of the BLANK function is as follows:


BLANK()


This syntax simply returns a blank value or empty cell.

Applications of the BLANK Function

Replacing Null or Blank Values

The BLANK function is commonly used to replace null or blank values in a column or table. This is particularly useful when you’re working with data sources that contain missing values. By replacing these null or blank values with the BLANK function, you can ensure that your data is accurate and complete.

Filtering Data

The BLANK function can also be used to filter data in Power BI. For example, if you want to exclude rows that contain null or blank values in a particular column, you can use the BLANK function in a filter expression. This will ensure that only rows with non-blank values are included in your analysis.

Calculating Averages

When calculating averages in Power BI, the BLANK function can be used to ensure that null or blank values are excluded from the calculation. For example, if you have a column that contains sales data and some of the values are missing, you can use the AVERAGE function in conjunction with the BLANK function to calculate the average sales value without including the missing data.

Examples of Using the BLANK Function

Example 1: Replacing Null Values

Suppose you have a table that contains customer data and some of the values in the “Age” column are missing. You can use the BLANK function to replace these null values with a blank value. Here’s an example:


=IF(ISBLANK([Age]),BLANK(),[Age])


This formula checks if the “Age” column is blank. If it is, the formula returns a blank value. Otherwise, it returns the value in the “Age” column.

Example 2: Filtering Data

Suppose you have a table that contains sales data and you want to filter out rows that contain null values in the “Product” column. You can use the following filter expression:


=FILTER(SalesData,SalesData[Product]❰❱BLANK())


This expression returns a filtered table that only includes rows that have non-blank values in the “Product” column.

Example 3: Calculating Averages

Suppose you have a table that contains sales data and some of the values in the “Sales” column are missing. You can use the AVERAGE function in conjunction with the BLANK function to calculate the average sales value without including the missing data. Here’s an example:


=AVERAGEX(FILTER(SalesData,SalesData[Sales]❰❱BLANK()),[Sales])


This expression first filters out rows that contain null or blank values in the “Sales” column. Then, it calculates the average value of the remaining sales data.

The BLANK function is a powerful tool in Power BI that can be used to replace null or blank values, filter data, and calculate averages. By understanding the syntax and applications of this function, you can ensure that your data is accurate and complete, and that your analyses are based on the most relevant information.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)