summarizecolumns() and Excel DRE (Detail Row Expression)

[100tsky71]

good day!

Why summarizecolumns() works in DAX Studio and doesn't work in Excel with DRE (Detail Row Expression)?

thank you!

[100tsky71]

Owen, good day and thank you!

I'm using Excel with SSAS Tabular and a measure as simple like 

but from Excel I've got a message

[OwenAuger · Accepted solution]

Thanks for the detail @100tsky71  🙂

If you are using SSAS Tabular, I believe "contextual SUMMARIZECOLUMNS" is not yet supported, since it was only fully enabled in products released from June 2024 (see here).

I believe it would work with Azure Analysis Services and Power BI, but for SSAS Tabular you would have to wait for a future update.

In the meantime, you should be able to write expression that achieves the same result using functions such as SUMMARIZE.

For example, instead of your current expression, you could write:

SUMMARIZE ( 'Calendar_01', 'Calendar_01'[Year] )

For some examples of more complex Detail Rows Expressions, see here:

https://www.sqlbi.com/articles/controlling-drillthrough-in-excel-pivottables-connected-to-power-bi-o...

Regards

[OwenAuger]

Hello @100tsky71 !

Could you clarify in what scenario this is not working for you, and provide a PBIX if possible for testing?

I tested connecting Excel to a Power BI model with a Detail Rows Expression defined using SUMMARIZECOLUMNS, and it appeared to work as expected when drilling through from a PivotTable.

Regards