Creating a dimension table (with hierarchy)

[Anonymous]

Hello  -  I am using our Orders table to create a Family Code dimension table for our products.   (just referencing the table, and deleting all other columns).  

However, we also have a hierarchy.   

So, a particular type of product, goes under a family code, along with other related products. Something like:

Bikes:  Mountain Bikes,  Cruising Bikes,  Double-Seated Bikes  

Tents:  Round Tents,  Square Tents, Rectangle Tents

or 

Family Code    Product Code

HAPO                HAPO-A

                          HAPO-B

                          HAPO-C

LAPR                  LAPR-A

                          LAPR-B

                          LAPR-C

I was going to just keep these two columns for my Dim table, but then quickly realized I would not have unique values on the Family Code.     So, my question is, what is the best practice to create a dim table when you have hiearchies?  

Thanks in advance!

[v-jingzhang]

Hi @Anonymous 

You may have interest in this nice blog (Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RADACAD) to have a deeper understanding about a dimension table in a model. I learnt a lot from it. 

Regards,
Community Support Team _ Jing

[HashamNiaz]

Hi !

Try creating a [Family Code] dimension with only unique family code values either using DAX or Power Qury (M);

DimFamilyCode = DISTINCT(Table[Family Code])

You can treplave [Table] with your existing table name. Now you can create a 1-many relationship between [DimFamilyCode] & [Table] based on [Family Code] column.

This technique is called bridging table. Similary you need to have a dimension with Distinct Product code & connect this table to [Table] using 1-many relationship based on [Product Code] column.

Regards,

Hasham

[CNENFRNL]

For a dimension table, it's NOT necessarily to make each column contain distinct values.

Typically, take the most common time table for example, Year and Month columns usually contain repeated items whereas Date column is distinct; because the granularity of Date is what we pay attention to.

[Anonymous]

Yes, that makes sense.   So as long as at least one column contains unique values, and that is the key column, then it can be a dimension table, correct?