topic summarizecolumns() and Excel DRE (Detail Row Expression) in DAX Commands and Tips

summarizecolumns() and Excel DRE (Detail Row Expression)

100tsky71 — Sat, 10 Aug 2024 23:25:01 GMT

good day!

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

thank you!

Re: summarizecolumns() and Excel DRE (Detail Row Expression)

OwenAuger — Sun, 11 Aug 2024 11:46:58 GMT

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

Re: summarizecolumns() and Excel DRE (Detail Row Expression)

100tsky71 — Sun, 11 Aug 2024 23:23:08 GMT

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

Re: summarizecolumns() and Excel DRE (Detail Row Expression)

OwenAuger — Sun, 11 Aug 2024 23:42:20 GMT

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-or-analysis-services/

Regards