use dax to create dimensions

[Anonymous]

I'm working on a dimensional model based off a single query which does all the joins so I have a my single table with both dimensions and facts.  I realize there are few ways to do this

1. Pull facts and dimensions from the backend but this would require several pulls and would prefer to process tables only once - which is what I have now.

2. Current solution we're using power query  The dimensions have surrogate keys which I create with SQL DENSE_RANK so all I do with PQuery is extract the columns (via duplicate) group them to get down to distinct rows and delete the non-key columns in the main table.  Works nice but it creates several dependencies and I have not found a way to refresh where the dependencies are recreated.  I thought pquery would just run all the steps again.

3. Use DAX which I have no idea where or how or what functions to use.  When I google "create dimensions with dax" I get mostly links on creating a master calendar.

Any help would be of great assistance.  TH

Anyone have a link or example of creating dimensions with DAX ? 

[CheenuSing]

Hi @Anonymous ,

Have you tried creating dimesnions tables using SUMMARIZE function ?

After you load the fact table, you can try to create dimension tables using the above function.

Cheers

CheenuSing

[Anonymous]

So that didn't work.  I could not drop the fields from the main table after creating the dimensions.  Apparently these tables are virtual.  Is there a function that will extract the fields I need ?

[CheenuSing · Accepted solution]

Hi @Anonymous ,

You will not be able to delete the columns used for creating dimension tables.

Alternately you can create a new fact table using  SELECTCOLUMNS(Table,Name1,Col1,Name2,Col2....).

In this approach you will have both the original fact table and the extracted fact table. An over kill.

If your intention is users should not see the dropped columns, you can hide them from the Fields pane of the fact table.

Cheers

CheenuSing

[Anonymous]

interesting idea.  I'll try it out.  thanks