Row or Column for performance and usability

[Anonymous]

Hello,

I have a report already built for which I am trying to look into performance improvement. I have a simple star schema and my fact table is originall in row storage. 

Month	Market	Customer	Department	Center	Measure	Value
M01	DZ	CustA	SS	GGH	Sales	100
M01	DZ	CustA	SS	GGH	Returns	200
M01	DZ	CustA	SS	GGH	Volumes	150
M01	DZ	CustA	SS	GGH	Amounts	50
M01	DZ	CustA	SS	GGH	Price	30

I have pivoted the columns to have all measures in columns instead of the above. What is better in terms of performance and usability? I find it counterproductive to calculate measures in such format 

Sales = CALCULATE(SUM(TABLE[Value]), TABLE[Measure] = "Sales") 

Month	Market	Customer	Department	Center	Sales	Returns	Volumes	Amounts	Price
M01	DZ	CustA	SS	GGH	100	200	150	50	30

Instead in this case I can do

Sales = Sum(TABLE[Value])

Could you please advise what is better and why? Initially my dataset is 5M rows and expected to grow and doing the pivot reduces is to 250K rows but of course with the extra columns.

[parry2k]

@Anonymous not sure what you mean? The option I talked about is keeping DAX simpler, not more complex. 

[parry2k]

@Anonymous as a best practice you always want to keep your data unpivoted and there are many reasons:

- you can filter on any of the attributes (measure in your case)

- you need only one measure and use the attribute column on column well of the matrix visual, or can be used on legends in other visuals.

- ability for drill through on attribute

- custom tooltip pages will be easy to build

There are may more reasons but the above list is a  good starting point.

[Anonymous]

but doesnt this limit the ability to create calculated columns and makes DAX more complex if I am using more than one of the measures in these calculatations. 

For example instead of doing SUM(TABLE[Sales]) I will need to do CALCULATE(Sum(TABLE[Value], TABLE[Measure] = "Sales")