In this article, I will show How to
Count Blank (NULL) values In Power BI Chart
Scenario
In Power BI, I have a data source from SQL Server Table that contains a field called “Project Status“.
The “Project Status” is not a mandatory field so it may hold a Blank or NULL value.
In Power BI, I have added a chart to show the count of “Project Status” values with the default configuration as below:
- In Legend: Project Status.
- In values: Count of Project Status.
Unfortunately, the configured Chart didn’t show the “No Date Items” as shown below
Show No data Items (Blank) In Chart Legend In Power BI.
- Click on the Field Name Arrow in the “Legend” Section.
- Check the “Show Items with No Data“.
The above settings will just show the Blank Label in the Chart Legend, but it doesn’t show “Items With No Data” in Chart Area In Power BI as shown below:
Show No data Items (NULL) in Chart Area In Power BI.
- Click on the Field Name Arrow in the “Values” Section.
- Instead of selecting “Count“, choose “Count Distinct“.
Show Blank Label In Power BI Output
The Power BI is now showing NULL / Blank values properly.
Unfortunately, the above chart doesn’t show the correct blank values count, it just shows a new legend item called blank, and a new blank piece in Chart Area.
In the next section, I will explain a workaround to
Count the Empty values in Power BI Chart
Count Blank (NULL) values In Power BI Chart Workaround
- From the above ribbon, create a new column.
- Add the below formula to check the NULL / Empty values then replace it with “Not Assigned” value to be countable.
[code language=”HTML”]
Proj Status = IF(ISBLANK(‘pjrep MSP_EpmProject_UserView'[Project Status]),”Not Assigned”,’pjrep MSP_EpmProject_UserView'[Project Status])
[/code]
- Drag a chart control, add the newly created column to the Legend and values as shown below:
- The blank / empty values should be showing now as “Not Assigned”.
Applies To
- Power BI Desktop.
Conclusion
In this article, I have explained How to Count and Display Null items in Power BI Chart?
See Also