Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |