cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

reduce number of dimension records based on the fact table

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)

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: reduce number of dimension records based on the fact table

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Helper IV
Helper IV

Re: reduce number of dimension records based on the fact table

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

Highlighted
Skilled Sharer
Skilled Sharer

Re: reduce number of dimension records based on the fact table

@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

Highlighted
Regular Visitor

Re: reduce number of dimension records based on the fact table

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors