Pivoting a Measure Under the Year Dimension in SUMMARIZE

[Anonymous]

Hi guys

How do I pivot a measure under columns in a SUMMARIZE statement?

For example, I can easily show a row count by department -

EVALUATE
ADDCOLUMNS (
SUMMARIZE ( fct, fct[Department] ),
"Row Count", CALCULATE ( COUNTROWS ( fct ) )
)

Department	Row Count
HR	34325
Payroll	23424
Ops	32424

But how would I show a row count by dimDate[Year] that's pivoted across columns? -

Department	Row Count	2013	2014	2015
HR	34325	12332	12434	9559
Payroll	23424	3432	12142	7850
Ops	32424	1212	23124	8088

Thanks,
Simon

[OwenAuger · Accepted solution]

With a DAX query, there isn't any way to either pivot or enumerate a column's values as column headers.

To do this with DAX you would have to do something awkward like this, with there being no way of making the years dynamic:

EVALUATE
ADDCOLUMNS (
SUMMARIZE ( fct, fct[Department] ),
"Row Count", CALCULATE ( COUNTROWS ( fct ) )
"2013", CALCULATE ( COUNTROWS ( fct ), dimDate[Year] = 2013 ),
"2014", CALCULATE ( COUNTROWS ( fct ), dimDate[Year] = 2014 ),
"2015", CALCULATE ( COUNTROWS ( fct ), dimDate[Year] = 2015 )
)

[Anonymous]

Thanks Owen, i feared as much  Your example is what I ended up doing unfortunately!

I'm genuinely surprised they haven't included this functionality.  I can kind of understand from a columnstore/tabular point of view why they haven't implemented it but it's frustrating for more advanced users!

[CheenuSing]

@Anonymous

If  the two tables fct and dimDate table are linked, then it is easy to acheive this.

1. Create measure ( not column ) called RowCount = Calculate(CountRows(fct)) 

2. Create a matrix table containing Department, TransYear

3. This will be able to give you the output. Sample Output

  

Where the row Total is the sum of the row count by year.

If your data tables and the output requirement are specific the share the data model and the output expectation.

If this works please accept this as solution and also give Kudos.

Cheers

CheenuSing

[Anonymous]

Thanks for the quick response CheenuSing!  I'm actually chasing a DAX solution similar to what I posted. 

[CheenuSing]

@Anonymous

I am not able to understand the need for adding to the fact table the count of rows and by year , when one may expect date as a column in the fact table.

Can you please elaborate the use case.

Cheers

CheeuSing

[Anonymous]

DAX is a query language similar to SQL. With this in mind, a user may query Power BI's xVelocity engine in a SQL-like fashion to transform data to a way that they require it.  I am not physically adding anything to the fact table.  The query that I posted is performed purely in memory and is outputted where I like - usually aggregated to a scalar value in a X function (e.g. SUMX) or even to a table in DAX Studio or a query table in Excel.

The output in this situation requires the use of pivot functionality and hence the reason for my post - I am not aware of pivot functionality in conjunction with SUMMARIZE so I was hoping that a DAX expert can help me out.

Simon

[CheenuSing]

Typo correction to point 

                   2. Create a matrix table containing Department, TransYear

should read as 

                  2. Create a matrix table containing Department, TransYear and values as RowCount

Cheers 

CheenuSing