Count Blank Values In Power BI

In this article, I will show How to

Count Blank (NULL) values In Power BI Chart

Count 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 items with no data in Power BI

Show No data Items (Blank) In Chart Legend In Power BI.

  1. Click on the Field Name Arrow in the “Legend” Section.
  2. Check the “Show Items with No Data“.

Show Items with no data In Power BI Chart

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 Items data in Chart Area In Power BI

Show No data Items (NULL) in Chart Area In Power BI.

  1. Click on the Field Name Arrow in the “Values” Section.
  2. Instead of selecting “Count“, choose “Count Distinct“.

Count Distinct in Power BI

Show Blank Label In Power BI Output

The Power BI is now showing NULL / Blank values properly.

Show Blank values in Chart In Power BI

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 Null values in Power BI Chart


Count Blank (NULL) values In Power BI Chart Workaround

  • From the above ribbon, create a new column.

New Column in Power BI

  • Add the below formula to check the NULL / Empty values then replace it with “Not Assigned” value to be countable.

Proj Status = IF(ISBLANK('pjrep MSP_EpmProject_UserView'[Project Status]),"Not Assigned",'pjrep MSP_EpmProject_UserView'[Project Status])

  • Drag a chart control, add the newly created column to the Legend and values as shown below:

Configure Chart In Power BI

  • 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s