Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
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 a.xyz.com|+91XXXXXa.xyz.com|+91XXXXXp.xyz.com|+91XXXXX34 min IN-BLR-XYZ
Should Come b.xyz.com|+91XXXXX---IN -BLR -XYZ 
Not c.xyz.com|+33XXXXXX   FR-XXXXXX
Not d.xyz.com|+44XXXXXX    
6 REPLIES 6
mickjaeger
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...  

V-lianl-msft
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,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

negi007
Community Champion
Community Champion

@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

amitchandak
Super User
Super User

@Anonymous , you can add +0 in measure

Anonymous
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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors