How does everyone build their tables so they can slice by all possible combinations?

[justlogmein]

I have been using Power Query and Power Pivot in Excel for a while and I have a feeling I am doing something the wrong way. I usually aim to build a table with every possible combination of slicer columns within Power Query and then do my calcs in Power Pivot in calculated columns (I pretty much ignore measures completely as most of my calcs are not aggregates).

For example, if I have a years worth of data and I need to slice down to individual days, I will create a table/list in Power Query with 365 dates. Then if I need a slicer in my charts that filters by say Project, I will merge my list of projects with this table, for each date. The goal is that I would create every single possible combination of slicers within the one table. The problem here is that for every column I add that I want to slice by, I make the table exponentially bigger. I don't know how to get around this, but I figured this is really inefficient. Power Query gets bogged down on every project I do.

I would like to know how others create their tables so they can slice by all combinations of many different slicers?

[v-jingzhang · Accepted solution]

Hi @justlogmein 

I agree that Star Schema Design is suggested in Data Model in Power BI. Here is the official documentation for it: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

You can store Dates and Projects in separate Dimension tables like Date table, Project table, Region table... These Dim tables can keep only uqinue values and are not very big. Then create relationships between Dim tables and Fact tables. Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. They contain dimension key values that exist in Dimension tables. You can drag columns from Dim tables into slicers to filter the data from Fact tables. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@justlogmein , You should prefer a star schema in power bi, not a single table

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/