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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sebastien_druon
Frequent Visitor

Handling null in one-to-many relationship

Hello

 

I have 2 simple tables with what I would expect to be a 1 to many relationship.

 

Problem 1

PowerBI however imposes a many-to-many relationship

sebastien_druon_0-1614803396208.png

Here are the table contents, the "Customers" table containing a null value:

sebastien_druon_1-1614803482818.png

sebastien_druon_2-1614803499357.png

How can I avoid powerbi creating a many-to-many relationship?

 

Problem 2

I created a simple table visual to display the number of tickets per customer.

I would expect Customer A and Customer B to appear, with a count of 0 for Customer A.

PowerBI does not display Customer A at all though

sebastien_druon_3-1614803708767.png

How can I solve that issue?

 

Many thanks!

 

PS: a copy of the pbix file here:

https://1drv.ms/u/s!AsdHsyy8cIM4jcAt62ZodGYi0CHMqw?e=WXeP7B

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @sebastien_druon 

#1  The cardinality type selected  in relationship depends on the data in the tables("SystemID"  contains blank value).If you force a one-to-many relationship between tables, the following error will be reported.

4.png

 

As a workaround, you can change the type of column 'SystemID' from "whole number" to 'Text'.

5.png

6.png

7.png

 

#2 
You need to enable the option "show items with no data" of filed "Data"

1.png

You can also directly create a new measure 'Count of ticket' to replace the original field"ticket" as below:

 

 

Count of ticket = COUNT(Tickets[Ticket])+0

 

 

 

2.png

 

Please  check my sample pbix file for more details.

 

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

View solution in original post

3 REPLIES 3
BrennoBBS
Regular Visitor

Was going crazy and creating a new related table with Dax just because it wasn't showing null values.

 

This info: "You need to enable the option "show items with no data" of filed "Data"" saved my life, thank you so much sir! 🙂

v-easonf-msft
Community Support
Community Support

Hi, @sebastien_druon 

#1  The cardinality type selected  in relationship depends on the data in the tables("SystemID"  contains blank value).If you force a one-to-many relationship between tables, the following error will be reported.

4.png

 

As a workaround, you can change the type of column 'SystemID' from "whole number" to 'Text'.

5.png

6.png

7.png

 

#2 
You need to enable the option "show items with no data" of filed "Data"

1.png

You can also directly create a new measure 'Count of ticket' to replace the original field"ticket" as below:

 

 

Count of ticket = COUNT(Tickets[Ticket])+0

 

 

 

2.png

 

Please  check my sample pbix file for more details.

 

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

Ashish_Mathur
Super User
Super User

Hi,

Create a third table which should list down all the unique ID's (without blanks).  Now connect the ID columns of the 2 tables to this third table.  Create a Many to One relationship wth cardinality of Single. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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