Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone!
I'm looking for some guidance regarding which method is the preferred one from a preformance standpoint. This is the case:
The dataset that I'm using here is just a small dummyset i'm using for experimentation.
This is the initial dataset:
And this is the desired outcome:
I've found 2 different ways to accomplish this.
Either I use this dax as a calculated column:
Test = LOOKUPVALUE(data[Jan];data[Produkt];data[Produkt];data[Månad];data[Månad]+1)
I end up with this result:
Or i duplicate the inital query, filter so that i only have a single month in each query and then merge them together, achieving the result i posted at the top.
Now imagine that this is not a 20 row table but a 20 million row table. where instead of having just january and febuary you have every year/month between 2015 and today.
The first alternative would require 46 calculated columns while the second would require 46 merges, which option would be better from a performance standpoint?
Also if anyone can propose a better way to do this I'm very much open to suggestions! 🙂
Br,
Johannes
Solved! Go to Solution.
the most efficient way always entails the use of physical relationships between tables as much as possible.
The Manad can be normalized into a dimension table and then you can create a relationship with your inifial dataset and drop the monthname in the columns section of the matrix
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Since your initial dataset contains 20 rows and the desired dataset contains a summarized version i.e. 10 rows, you will have to create a summarized table.
But first, create the Jan and Feb sales:
Add columns named Jan and Feb
Jan = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Mainad] = 1 ), FILTER ( Table1, Table1[Product] = EARLIER ( Table1[Product] ) ) )
Feb = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Mainad] = 2 ), FILTER ( Table1, Table1[Product] = EARLIER ( Table1[Product] ) ) )
Then Summarize:
Table = SUMMARIZE ( Table1, Table1[Product], Table1[Jan], Table1[Feb] )
the most efficient way always entails the use of physical relationships between tables as much as possible.
The Manad can be normalized into a dimension table and then you can create a relationship with your inifial dataset and drop the monthname in the columns section of the matrix
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
98 | |
90 | |
82 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |