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

[Anonymous]

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 Condition	User Name | Phone Number	Caller	Callee	Duration	Office
Should Come	a.xyz.com|+91XXXXX	a.xyz.com|+91XXXXX	p.xyz.com|+91XXXXX	34 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

[mickjaeger]

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.

[PaginatedDino]

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]

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]

@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.

[amitchandak]

@Anonymous , you can add +0 in measure

[Anonymous]

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