cancel
Showing results for 
Search instead for 
Did you mean: 
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

2 REPLIES 2
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

Ashish_Mathur
Super User III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.