March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to Solution.
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!
@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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |