Normalizing one flat table into star schema - giving wrong numbers

[YukiK]

I'm trying to create a star schema from one big flat table. I'm doing this with power query along with sql native queries (i don't have permissions to create views). I'm extracting multiple dim tables, but here is my process:

1. Extract dimension columns (i.g. area, region, sales office) and put them in a separate table
2. Remove duplicates based on columns that uniquely identify each row
3. Add an index column that uniquely identifies each row
4. Join this newly created dim table to the fact table on the columns that uniquely identify each row in the dim table
Remove these columns and keep the index column from dim table

Dim tables are connected on these index columns. But when I use a column from a dim table with a measure from a fact table, they give me wrong numbers. I've been trying to find the solution, but I have no idea at this point.

I appreciate any help!!!

[YukiK · Accepted solution]

I ended up figuring out the issue on my own. And it was that I needed to sort tables before adding index(SK) columns and join dim tables to the fact table.

I didn't encounter this before, but this time, the data was huge and I suppose the distribution of SKs in fact table from dim tables got messed up. A lesson learned!

[Greg_Deckler]

@YukiK The logic seems sound. What kind of measures are you using and are your relationships in the right direction?

[YukiK]

Not really using measure at this point. The relationships directions look correct and they base in SK columns I added. What's weird is that when I join, let's say, on category column (define each row uniqeuly) rather than SK columns, it works.

Somebody mentioned "auto-exist" migth be doing something...