How to create a dimension table from a fact table using DAX?

[Anonymous]

Hi guys,

I'm working on my data model, so I'm trying to create a dim table from my fact table. I don't want to do it in Power Query because I only got one source of data, so I don't want to keep duplicating my source whenever I need a dim table, I already tried that and it triplicates the size of my file, slowing down the performance. 

In my fact table I've got an "Assignment Group" column, which tell us the group that is assigned to the record. This column has repeated values, as several records can belong to a same group. 

My goal is to create an ID for the "Assignment Group" column so it can behave as a Foreign Key in the fact table. Then, create a dim table that has the ID as the Primary Key and a second column with the Assignment Group, removing duplicates (Create a catalog). Remove the Assignment Group column from the fact table and create a relationship between the fact and dim tables through the IDs. 

Could someone advise in how to do this in DAX please?

Thanks!!

OV

[PaulDBrown]

@Anonymous 

You can create a dim table for the "Assignment Group" using:
Dim Assignment group = DISTINCT(Fact_Table [Assignment group])

You can then join the common fields in a one-to-many relationship (you don't need the numerical key for this). If you need a column to establish a sorting order, add a calculated column to the Dim table using RANKX or a SWITCH function.

[Anonymous]

@PaulDBrown thanks for the input!

I read the storage is lower if you have a numeric ID in your fact table and your actual data in the dim table. Actually the reason I'm doing this is to optimize the storage and have a better performance while slicing by assignment group. 

If I just create a relationship between my fact and dim table with the actual assignment group, am I still getting any enhancement here?

OV

[PaulDBrown]

@Anonymous 

The thing is in order to have a numeric key in your fact table you are going to have to create a calculated column  to assign the number for each value anyway (adding columns should be avoided since they hinder performance).

Alternatively you could do it in Power Query (best practices actually generally recommend doing table transformations/creations as early as you can in the process (so at the source -> if not PQ -> if not Dax).

I don't think you will notice any significant delay between using a numeric key and a text key anyway, but try it out.

PS. BTW, did you try "referencing" your fact table to create the DIM table (instead of duplicating it)?

[Anonymous]

@PaulDBrown 

As I'm unable to mess with the source file, this has to be done in Power BI.
I used Power Query actually, but I had to do this with several dim tables so I'm duplicating the source, performance goes down and the file grows a lot, so I was advised to use DAX instead, calculated columns.

I'm not using referencing because as I understand, at some point I have to remove my Assignment Group column from the fact table, so doesn't that create an inconsistent dependency issue?

[v-yiruan-msft]

Hi @Anonymous ,

According to your description, it seems that you want to add Assignment Group dimension table, and then replace Assignment Group in the fact table Assignment Group ID with numeric type to improve performance. Adding a dimension table(calculated table) and replace Assignment Group(calculated column) in the fact table using DAX, these increase the model size and consume more RAM. Just as @PaulDBrown said, these operation may not improve performance. Maybe you can try the methods in the following links to optimize your data model.

Optimize a model for performance in Power BI

A comprehensive guide to Power BI performance tuning

Power BI Performance Optimization Tips

Best Regards

[Anonymous]

Thanks for your inputs guys!!

I created as much dim tables as possible in Power Query, did not mess with DAX and actually I'm getting better performance without the Assignment Group dim table versus having the source duplicated and creating it from there. 

@v-yiruan-msft 

I'm gonna take a look to these resources, thanks again for your replies!