topic Re: Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions in Report Server

Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions

JayZheng — Thu, 05 Apr 2018 21:24:03 GMT

Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions such as SAMEPERIODLASTYEAR(), FIRSTDATE(), LASTDATE()...

Any thoughts? Given that I don't want to use import mode.

Re: Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions

Lorian — Wed, 13 Jun 2018 10:58:12 GMT

What data source are you trying to query? If it's a database, then you would have to use SQL and not DAX in the query. If its PowerQuery then I have no idea.

Re: Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions

Anonymous — Wed, 13 Jun 2018 12:48:17 GMT

you would have to use sql if its a sql db, not dax. DAX would be after you get the data loaded.

Re: Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions

JayZheng — Thu, 21 Jun 2018 20:54:13 GMT

Thanks for your response, however, I don't think we are on the same page here. Let me explain, all my datasets are ready for report in SQL server as a view, which I can use direct query option to connect via PBI connection. The reason I use DAX is for the on screen calculation when user multi-select different charts and graphs. Now, I want to point one thing out that, it works when I use the normal PBI which is connected to the O365. But it won't work when I deployed it to the PBI on-premise server. Hope that clarifies things. Again, it's not connection issue and I do not want to use "Import mode" though I know it will work. Thanks.

Re: Power BI server (March 2018 released version) Direct Query CANNOT use built in DAX functions

Lorian — Fri, 22 Jun 2018 09:00:44 GMT

I see, sorry, then I don't think I can help.

Are you using DAX to create new columns or new measures? Sometimes I find that some DAX doesn't work in measures but does when I use it to create a new column. But the problem shows itself in the desktop book already before I save to the RS. Does the error specifically refer to DAX as the issue?

I've never really been able to get these SAMEPERIODLASTYEAR() type functions working, even in desktop. There are some relatively messy work-arounds floating about that use filters instead. Maybe one of those will work? I found this very useful: http://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question

Or perhaps calculate it in your SQL server view instead of PBI?

The problems I've had with DQ on the on-premise RS so far have been access permission related as the user must have direct access to the server themselves to view it; you can't embed another user's credentials (like a shared user) in the data source like you can with import mode.