Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors