Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to include null values or blank data in power bi table

Hello All , 

I got stuck in one power bi chart issues. I am creating a table chart where I am publishing user activity details from simple column values from the table. I am using 1 fact table and 2 Dimension table having 1 to many relations on both. 

Now I want to include null values in my report in order to see the users with no activity . While using Show items with no data where I am taking user column from one table and activity details from another it was working fine , but as soon as I am adding geographical data this is not working. When I filter the data by Office/city (for e.g. India -Bangalore ), I am getting overall data from all countries /cities along with matching users office details. Please help me in correcting this. 

In below table I have represented my condition , for e.g If I select India and offices It should give me all rows including null and omit other countries & the same should be done vice versa if I select another countries or cities . 


Ideal ConditionUser Name | Phone Number Caller Callee Duration Office 
Should Come|||+91XXXXX34 min IN-BLR-XYZ
Should Come|+91XXXXX---IN -BLR -XYZ 
Frequent Visitor

Honestly, this functionality is so dumb!  Why would Microsoft program Power BI to exclude rows where a column has a null value???  I'm so, so baffled by this functionality.  


In my case, I have a SQL view (single table) that I pulled into a PBI dataset to record historical EEO snapshots for our HR department.  The table key is the snapshotID, which is never null.  The report should show all rows that match the Power BI filters but for some reason the decision was made to exclude entire rows where a single field was left null.  In my case as an example we have:

(SnapshotID, EmployeeID, EmployeeName, GenderIdentity, SnapshotDate)

(123456, 100731, Mickey Mouse, null, 2022-04-28)

Since Mickey Mouse didn't input a gender identity the result is that Power BI ignores the entire row of data.  I just don't get it, this is so, so weird and rediculous.  


Rant complete, thanks for listening.

I'm experiencing this now and I second your rant... It does not make any sense to be obliged to fill in defaults in order to work around this behavior...  

Community Support
Community Support

Hi @Anonymous ,


This may be caused by the filtering direction. Try setting the filtering direction to both. If you don't get the results you want, you can continue to use "crossfilter" function to create calculated columns or measures.

If the problem persists,could you share the sample pbix or the sample data of the three tables referring and the relationships?

(Please mask any sensitive data before uploading)


Best Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User
Super User

@AnonymousWhen you are importing your data, in powerquery window you could replace your null values with anything like "No values". This way one step will be added to your import process. So everytime you refresh data, it will automatically replace null values. Please refer to below screenshot for details.


Null Values.PNG

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me on linkedin

Super User
Super User

@Anonymous , you can add +0 in measure

Not applicable

Thanks for the prompt support. But there is no measure in my table, Duration column also I am calculating in my SQL query.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors