Unable to create a 1:M relationship between Dim and Facts table

[acao-96]

Hello everyone,

I am trying to create a 1:Many relationship between two tables, but for some reason when I try to create a relationship it defaults to Many to Many and if I try to change it I get the 'cardinality not valid' error (the relationship was built based on the PIT Lifecycle Universe in the Facts table and the Orig Lifecycle Universe in the Dim table). I did notice that when I added the blank value in the Dim table, the relationship was no longer 1:M and it seemed like the relationship didn't work either when I tried to filter for the blank values in the Lifecycle Universe column in the Facts table using the PIT Lifecycle Universe column in Dim table.

Could you please let me know what I did wrong here or if I am missing something? TIA!

Below are my tables:

Dim TB

Dim Table

Facts TB

[ronaldbalza2023]

Hi @acao-96 , create a Unique ID on your DimTable and merge it in your fact table 😉

(3) Power BI Tutorial | From Flat File To Data Model - YouTube

[acao-96 · Accepted solution]

Hi there,

Thank you so much for your response. Since there are several fields similar to the Lifecycle Universe, I wasn't planning on adding additional columns to the Fact table because that would end up with too many fields.  Also, I think the problem here is that I don't know how to handle the blank value in the Dim table. If I were to merge the Dim table with the Fact table like you said (I am assuming based on the Orig Lifecycle Universe field), would the blank values in the 2 tables be able to be merged?

Thanks!