cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Pivoting a Measure Under the Year Dimension in SUMMARIZE

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

1 ACCEPTED SOLUTION
Super User
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
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 )
)

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
7 REPLIES 7
Super User
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
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 )
)

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Anonymous
Not applicable

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!

Community Champion

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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

Community Champion

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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

Community Champion

Typo correction to point

2. Create a matrix table containing Department, TransYear

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

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!