Forum Discussion
Post PatronCalculate between different granularities
Hi there.
The key to good Power BI reports is a sound and simple model. Power Query (M) can also be optimized like DAX but one has to know HOW. Please read the documentation on the M language, read about eager and lazy evaluation rules and apply them wisely. A good source of information on Power Query and M is Chris Webb's blog at
and also this tutorial
I have read this all and know how to optimize M so that it's blazingly fast. I suggest you do the same. Moving data mashup operations into DAX is never the way to go unless... you want to get into deep, deep trouble later on; it's like a time bomb that will blow up rather sooner than later.
Also, please note that if you don't use the dimensional design (separating dims and facts), you'll sooner or later be producing wrong numbers. This is due to the fact that SUMMARIZECOLUMNS optimizes the retrieval of attributes from tables using the auto-exist feature (read upon auto-exist in DAX on www.sqlbi.com; just use the search engine on the page).
Secondly, the golden rule of dimensional modeling is this:
Your dimensions should be short but wide and your fact tables should be narrow but long.
There's NO EXCEPTION TO THIS.
Now, please look at the attached file. It has a model as it should be set up and enables you to calculate everything you need simply and fast. This IS exactly how it should be done. You won't get anything better in terms of speed and simplicity.
Please try to learn how to optimize operations in M. Do not shoot yourself in the foot.
Best
D
Post PatronAnonymous This model is just a simple model for testing purpose. I want to find a DAX way of calculating between fact tables with different granularity. I know the solution for lower grain to higher grain is SUM, I'm looking for the opposite solution for higher grain to lower grain.
I am coming from here. I am super confused because everywhere on the Internet I read different things about making a star schema design. Some say, merge as much as possible in as few tables as possible, others say keep the dimension tables apart - and then I can't create a hierarchy anymore in Power BI... From the comments in my linked topic above I concluded I should keep the dim tables apart...
I have fact tables at different granularity, some data is available in one fact table, while it should be available in the other fact table, and vice versa. One approach would be to bring everything down to the lowest grain in Power Query but then I get very long running refreshes and it's difficult to maintain. I want to be able to easily add future tables and columns and refresh only those parts. So my current philosophy is to keep separate tables and when something is available at higher granularity I divide it in a way to fill the lower granularity tables and vice versa...
Previously I tried to do it all with one big table, or maybe 2, but my power query code got huge and unmaintainable. If I wanted to do something simple like introduce a new column, I would have to verify and change all steps in power query. Every time a new feature was requested it was too much work in PQ. With doing less in PQ and more in DAX I think I can be more flexible and I can reduce refresh times....
Looking forward to hear back.
Solution SageI appreciate how confusing it is. If you'd like to do a screen share I'll walk you through changing your data to a Star Schema. Just send me an email with a good time to talk. [email protected]