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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.