Forum Discussion
Create New Table Displaying Unique Values From EACH Column - ignoring row relationships
Hi Anonymous ,
It sounds like you need to create dimension tables for each of the dimensions that you want to slice on.
You can create these dynamically in Power Query by using the following code in a new blank query:
let Source = Table.Distinct(Table.SelectColumns(inputTable, "Manufacturing Plant")) in SourceDo this for each of the columns that you want to be able to slice against, apply all the tables to your data model, then relate dimensionTable[columnName] to factTable[sameColumnName] on a 1:MANY basis.
Use the dimension table column in any filters/slicers and it will correctly filter your fact table.
The principle you are looking for is STAR SCHEMA, and more info can be read here:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
Hi Anonymous ,
It sounds like you need to create dimension tables for each of the dimensions that you want to slice on.
You can create these dynamically in Power Query by using the following code in a new blank query:
let
Source = Table.Distinct(Table.SelectColumns(inputTable, "Manufacturing Plant"))
in
Source
Do this for each of the columns that you want to be able to slice against, apply all the tables to your data model, then relate dimensionTable[columnName] to factTable[sameColumnName] on a 1:MANY basis.
Use the dimension table column in any filters/slicers and it will correctly filter your fact table.
The principle you are looking for is STAR SCHEMA, and more info can be read here:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
Thank you so much for taking the time to reply, this has worked perfectly *chefs kiss*