Performance guidance

[tex628]

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

[Anonymous]

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] )

[LivioLanzo · Accepted solution]

@tex628

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