Forum Discussion
Calculate between different granularities
So I have above datamodel.
A (highest granularity) has multiple rows of B, and B has multiple rows of C (lowest granularity). See below data structure.
I'm trying to make measures that can calculate between different granularities.
Let's say I have a fact called 'C Feit' in table 'Feit C level', I can then simply use SUM( 'Feit C Level'[C Feit] ) ) to calculate the values for higher granularities.
But I'm stuck with doing the opposite. I obviously need to divide by the count of rows, but I can't figure out how to make it work.
Let's say for these fact values:
A Index 1 = 100
A Index 2 = 80
A Index 3 = 60
A Index 4 = 40
Below fact values should be calculated:
C Index 1 = 25
C Index 2 = 25
C Index 3 = 25
C Index 4 = 25
C Index 5 = 20
C Index 6 = 20
C Index 7 = 20
C Index 8 = 20
And so on...
Anyone? Thanks in advance!
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
13 Replies
DAX provides a set of functions for creating parent/child hierarchies of arbitrary depth. Take a look at the pattern. https://www.daxpatterns.com/parent-child-hierarchies/ Once you have the hierarchy I think it will be easier to do the calculations you need.
- Why do you use this convoluted setup? Isn't it more logical to have just one fact table with the lowest granularity and connect the dims to it in a star schema? Then you won't have to do any stunts in your code and the code will be much simpler and faster.
Please normalize the model in the sense of star schema and you won't have any problems at all with calculating anything. Also remember to hide all columns in the fact table and slice only through the dimensions.
Best
DAnonymous 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.
I 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]
By the way, here is my pbix file: https://we.tl/t-3UrraYMDnE
