Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
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
ADDCOLUMNS (
SUMMARIZE ( fct, fct[Department] ),
"Row Count", CALCULATE ( COUNTROWS ( fct ) )
)

DepartmentRow Count
HR34325
Payroll23424
Ops32424


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

 

DepartmentRow Count201320142015
HR3432512332124349559
Payroll234243432121427850
Ops324241212231248088

 

 

Thanks,
Simon

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User
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
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 )
)

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

Thanks Owen, i feared as much Smiley Sad 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
Community Champion
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

  Capture12.GIF

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. 

@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

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

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

Proud to be a Datanaut!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.