What is the best approach when not having unique values to build the data model?

[selected_]

delete

[Ahmedx]

If I understand correctly, you need to read this topic in the book

[danextian]

What do you mean by " because CategoryID column in FactTable equal distinct and unique values compare to the Category table"?
If Dim_Category[ID] and Dim_Person[ID] both contain unique values, you should be able to build a one-to-many relationship from these dimension tables to the fact table unless they contain a blank value.

[selected_]

But I have miss matching in some columns when i compare these IDs in a visual table, some of the ID that are inside the fact table shows empty in Dim_Category(ID) and the ID inside facttable contains null values but none null values in the Dim_Category(ID).

as you can see here:

[v-dineshya]

Hi @selected_ ,

Thank you for reaching out to the Microsoft Community Forum.

This typically occurs when missing relationships due to nulls or blanks, duplicate values in the dimension table and Unmatched keys between fact and dimension tables.

Please check below things.

1. Remove rows with contains nulls or blanks in key columns if they are not required. Replace nulls with default values to ensure matching. Eliminate duplicates in the dimension table to allow relationship creation.

2. Use calculated columns for row-level operations and measures for aggregations. Use Query Editor to auto-detect relationships and add missing ones manually.

3. Power BI only shows combinations that exist in the model. If tables are unrelated, Power BI may issue an unconstrained join error. Use cross joins and ensure relationships are defined.

Please refer below Microsoft documents.

Tips and tricks for creating reports in Power BI - Power BI | Microsoft Learn

Show items with no data in Power BI - Power BI | Microsoft Learn

Model data with Power BI - Training | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @selected_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @selected_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @selected_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[danextian]

Hi @selected_ 

Those are non-matching rows - they exist in the fact table but not in the Dim_Category table. Ideally, your dimension table should include all the keys present in the fact table. That said, a one-to-many relationship should still function correctly as long as Dim_Category[Category ID] doesn’t contain blank values. It’s worth looking into why some items are missing from your dimension table.