reduce number of dimension records based on the fact table

[amien]

Let say i have 2 tables loaded:

Fact table and Product table.

I would like to reduce the number of records in the Product table. I only want to keep the Product records where there is an existing fact for it.

So if the distinct number of ProductID's in the Fact table will be 25, Then the number of Distinct values of ProductID in the Product dimension table should also be 25 (Allthough there might be 100 Distinct ProductID available in the product dimension table)

[Seth_C_Bauer]

@amien If you are using SQL or any database for that matter as a datasource, just make your product table (dimension) a view.

Do an inner join to the fact table to return only the products in the fact table. Otherwise, if you want to do it in the model, There is most likely a way to perform this in Dax, but I don't know off the top of my head.

[amien]

I would like to know a way to do this in PowerBI Desktop. And i would like to keep 2 seperate tables.

[GilesWalker]

@amien - Its sounds like you could do a merge queries in the Query Editor. Merge the Fact Table with the Product Table using a Left Outer join. This should take everything from your Fact Table and bring in only the matching items from the Product Table. You will need to have a common value to match the tables. 

I would rename this table, e.g. Fact/Product table. Then if you need to have the Fact Table by its self then I would go to get data and download this table again.

Hope this makes sense.

Giles

[KA]

Hi,

I still find this a very interesting question. Any insight into best practices here are very welcome.

A couple of thoughts / questions about this:

• Do you try to limit the number of Dimension table records (based on the fact table) via SQL
• or indirect via SQL by getting this to work in the M language : query folding
• or in memory after the sql query has been executed
  • => in M
  • => or DAX

Any insight into the best practices in PowerBI for this question is greatly appriciated.