Welcome to Excel Jet Consut blog post. In my previous post on COALESCE DAX Function in Power BI, I show us how to use COALESCE DAX Function which returns scalar value coming from one of the expressions or BLANK if all expressions evaluate to BLANK. In this tutorial, we are going to learn how to fill blank rows with value zero (0) for cells that are blank. Over in Excel Data Model, we do not have COALESCE Function as as the time of publishing this blog post, however, we are going to achieve the same outcome using different but simple method of writing a DAX formula. Let’s get started
Sample Data
In the caption below, we have our sample data with two columns. In column B which contains the Total, we notice that quite a handful of values for some Account Managers are empty for whatever reasons. The Data is Formatted in Excel Table and renamed Sales_Data
Load To Data Model
The next thing we need to do is to load the data to the Data Model (Power Pivot) where we are going to write a simple DAX Measure.
- To load the data, click anywhere inside the dataset and execute ALT + BY. The B is ALT shortcut to select Power Pivot tab while the Y Add to Data Model.
In the caption below, we have our data loaded to the data model
Create PivotTable Report
We want to create a PivotTable Report from the Data Model
- In the Home tab of the data model, click on PivotTable and drop in cell D1 of the Existing Worksheet
- Drag and drop Account Manager and Total into the Rows and Values areas respectively
From the caption below, we notice that Account Manager, Andre Fuller, Erin Smith, Ken Black, Margaret Peacock and Odella Nelson which all have blank Total from the source data to the left. They are not on the PivotTable Report to the right
This is how Data Model handles blank cells which is not what we want. We want to see all the Account Managers name and for those who have blank rows, we want to return 0 which is a recognized value.
DAX Measure
Therefore, we are going to write a simple Calculated Column DAX Measure using IF and ISBLANK DAX Functions
- Execute ALT + Tab keys to return to the data model
- Double-click in the Add Column to rename the column and click Enter
- In the formula bar, execute the DAX formular: =IF(ISBLANK(Sales_Data[Total]),0,Sales_Data[Total])
In the caption below, we can see from the newly created Calculated Column that Account Managers with blank cell have 0s which is reasonable
Use DAX Measure
The next thing to do is to return to our PivotTable Report in Excel and use the DAX Measure
- Execute ALT + Tab to return to Excel
- Drag and Drop New Total measure into the Values area
We can see that all the Account Manager with blank Total do have zero (0) returned.
This is exactly the result we achieved using the COALESCE DAX Function in Power BI