Showing CAGR in Direct Query Model without Calculate function

[dc189]

Hello,

I have a direct query model and want to calculate CAGR but the calculate function doesn't work with direct query (https://powerbi.tips/2016/05/measures-calculate-cagr/). Is there an alternative to calculate function in direct query, to calculate CAGR?

Thank you. Would appreciate some help.

[Eric_Zhang · Accepted solution]

@dc189

The link returns a 500 error and can't be loaded when I post. If you could share any information on the CAGR formula, maybe we can replicated the same in DAX.

By the way, in case you're talking about the measure limitation in DirectQuery mode, you can try to click "File"->"Options and settings"->"Options"->"DirectQuery"->check "allow unstricted measures"

[dc189]

Thank you! Worked perfectly! Although I wonder why MS would restrict certain measures from DAX as default.

[Eric_Zhang]

@dc189

For performance improment in DirectQuery mode, check the DirectQuery limitation.