Yet another cardinality issue (one to many)

[Anonymous]

I'm new to Power BI, but not to cardinality. I've been trying to link two tables, which are both a dump from our database. Table 1 has a field which is BY DEFINITION unique. It can not contain duplicates, because our system refuses that.

The other table can have zero, one or multiple rows for the same item.

So it is a clear case of 1-to-many.

Yet, PBI keeps forcing me to use a many-to-many relationship. I've tried making my own table with less data, that worked fine. But on the real data...nothing....

why?

Is it because table B has some fields which contain no data (for instance "end date" isn't always applicable -yet-)

[v-alq-msft]

Hi, @Anonymous 

Normally, Power BI Desktop can automatically determine the best cardinality for the relationship. If you do need to override the automatic setting, because you know the data will change in the future, you can chagne it with the Cardinality control.

You can manage how Power BI treats and automatically adjusts relationships in your reports and models. To specify how Power BI handles relationships options, select File > Options and settings > Options from Power BI Desktop

For further information, please refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Well...

it DID automatically decide on the cardinality. BI says its n-to-n. But it isn't. However, it refuses to accept any other cardinality.

Based on earlier replies, and some pondering over the weekend, could it be that some cells (in other rows) are empty, standing in the way of the analysis? Would it be solved if I just typed "empty" in each of them?

[amaleranda]

@Anonymous ,

would be helpful if you could uploade a dataset that occurs the issue. then would be able to investigate why it would not allow you to create one to many relationship as you expected.

[Anonymous]

I'm afraid I can't do that...confidentiality and such...

is there a list of requirements to which data has to comply in order to BI to work properly?

[FarhanAhmed · Accepted solution]

Can't filter the NULL value in Power Query Edit in Table 1? I don't think this value will be used in your fact table.

[pranit828]

HI @Anonymous 

"Table 1 has a field which is BY DEFINITION unique".

This Table 1 has to be Primary(Unique and Not NULL column)

[Anonymous]

Ah, right...

Said value is indeed unique and cannot be null (indeed). Our system won't make a row otherwise.

[amitchandak]

@Anonymous , 1 side table should not have any duplicate key column. Also, it can not have a null value too.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships