CODEWITHSUNDEEP
powerbi
Randy Minder
Randy Minder

Reputation: 48522

Cannot create a 1:M relationship

In Power BI, I am attempting to join a dimension table with a fact table. The dimension table has approximately 1.1M rows in it (a lot I know for a dimension table). All the values are unique. When I attempt to join this to the fact table, PBI automatically creates a M:M relationship. When I attempt to change this to a 1:M, I get a message saying "The cardinality you selected for this relationship isn't valid".

Here is the query that generates the dataset. As you can see, it's impossible for there to be duplicates.

SELECT DISTINCT
   [TranDesc] as TransactionDescription
FROM [dbo].[dGLTranDescription];

Why would I get this message?

Upvotes: 2

Views: 10645

Answers (2)

Monstah
Monstah

Reputation: 415

If you have removed duplicates on the relationship column and it still considers it as invalid cardinality, try running Text.Clean on that column prior to duplication removal. I've had a special character but the removal of duplicates on the query, the values counted as different there but once imported they were considered the same.

Upvotes: 0

Ilya Berdichevsky
Ilya Berdichevsky

Reputation: 1298

Try to validate that Power BI seeing values in the dimension table as unique. Depending on your data, source system and PowerBI may see it differently.

Here are suggestions from https://community.powerbi.com/t5/Desktop/The-cardinality-you-selected-isn-t-valid-for-this-relationship/td-p/73470

1.

Create two measures to verify in Power BI:

TotalRows = COUNTROWS('DimTableHere')
DistinctRows = DISTINCTCOUNT('DimTableHere'[DimTableJoinColumnHere])

After create those two measures, place them in two card visuals, if results are different, it means there are duplicate values in your Dimension table.

2.

If you had duplicates when first creating relationship and now you don't, deleting the relationship and recreating it may resolve it.

Upvotes: 6

Related Questions