Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
in Power BI by 1 1 2
recategorized by

I am having issues creating a one to many, many to one and one to one relationship in my data model In Power BI and I got The Cardinality you selected isn't valid for this relationship. Howerver, It only allows many to many relationship and this doesn't work with the Related() function.

Power BI The Cardinality you selected isn't valid for this relationship

by 35 40 47
1 0
In case, you can't make relationship between two tables try to use LOOKUPVALUE, check the difference at  https://devoworx.net/power-bi-related-vs-lookupvalue-dax/

1 Answer

1 like 0 dislike
by 151 169 345

Why you got The Cardinality you selected isn't valid for this relationship?

In Power BI, you got this error "The Cardinality you selected isn't valid for this relationship" when trying to create a relationship between two tables in Power BI because one of the tables doesn't have unique values, so in this case, you can create a One-Many or Many-One relationship and you just able to create a Many-Many relationship.

Note: you can't use the RELATED function between two tables doesn't have relationships. Otherwise, you should use LOOKUPVALUE!
For more details, Please check Power BI: RELATED Vs LOOKUPVALUE DAX

How to solve The Cardinality you selected isn't valid for this relationship in Power BI?

To can create One-Many or Many-One relationship between two tables, one of them doesn't have a unique value you have to do the following:

  1. Reference one of your tables and consider it as a Junction Table between the other two tables.
    Reference table in Power BI
  2. Use Power Query Editor to
    • Remove the duplications using Remove duplicates
    • Replace blank, null, and errors values using replace values or filter functionalities.
  3. Create a new relationship as One-Many between the newly created table and the other tables.
  4. Try to use the RELATED function that should be working properly now.

See also, what's the difference between Reference vs Duplicate in Power BI

If you don’t ask, the answer is always NO!