cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dagurkuden Frequent Visitor
Frequent Visitor

Some dax functions does not work on PBRS

Hi all;

Some functions does not work  like dateadd,totalmtd,totalytd etc.  in direct query mode

 

And I  wanna say everthing works at Power BI Desktop RS .The problem starts when I published  to the Power BI Report Server.

 

Also marked "Allow unrestricted measures in Direct query mode"

 

Im getting

 

MdxScript(Model) (19, 33) Function 'DATESMTD' is not supported in DirectQuery mode. 

MdxScript(Model) (13, 4) Function 'DATEADD' is not supported in DirectQuery mode.   etc.

 

errors.

 

Which dax functions are working on Power BI Report Server?Is there any list of them?

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Some dax functions does not work on PBRS

Hi @dagurkuden,

 

I got information that unfortunately we do not have any information on road map to consider near future. I would request to create comment on forum https://ideas.powerbi.com/forums/265200-power-bi , So that product team will consider the same based on priority and requirement.

 

Regarding the error, please double check if the Allow unrestricted measures in Direct query mode option is enabled. Also as the Values() function return in a column of unique values, please modify the measure like below: 

 

Measure = CALCULATE(SUM('Product'[Amount]),FILTER(ALL('DateVal'),'DateVal'[DateCol] in VALUES(DateVal[DateCol])))

or

Measure 2 = CALCULATE(SUM('Product'[Amount]),ALL(DateVal),VALUES(DateVal[DateCol]))

 

As I tested, it can work fine both in desktop and report server. 

 

q1.PNGq2.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Moderator v-qiuyu-msft
Moderator

Re: Some dax functions does not work on PBRS

Hi @dagurkuden,

 

I can reproduce this issue on my side as well. Already reported it internally, will update to you once I get any information. 

 

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-qiuyu-msft
Moderator

Re: Some dax functions does not work on PBRS

Hi @dagurkuden,

 

DAX we are using are not supported(Dateadd, DATESYTD), below are list of DAX functions suppots in Direct query mode.

 

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

 

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquer...

 

Best Regards,

Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dagurkuden Frequent Visitor
Frequent Visitor

Re: Some dax functions does not work on PBRS

hi @v-qiuyu-msft

 

I did not try all but The "Filter " is listed but not working.

How can I be sure the list?Can you say something about a time intelligence function and others or can you give an information about road map ?

 

Most of our customers were using Power BI cloud  ,now they are returning to on prem.We can not explain to them that some functions does not work at power bi report server.

 

 

Thank you.

Moderator v-qiuyu-msft
Moderator

Re: Some dax functions does not work on PBRS

Hi @dagurkuden,

 

As you can see from the document, the Filter() function is supported in the measure. Based on my test in Power BI desktop optimized for report server 2.51.4885.1423 64-bit (October 2017), create a measure like this: Measure = CALCULATE(SUM('Product'[Amount]),FILTER(ALL('Product'),'Product'[Product]="Car"))

 

After publish the report to report server version 14.0.600.437, the measure still work. Please update your report server version and test again with a measure. 

 

Regarding the time intelligence functions, I will consult it internally then back update here if I get information. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dagurkuden Frequent Visitor
Frequent Visitor

Re: Some dax functions does not work on PBRS

hi @v-qiuyu-msft

 

You are right that filter function is working.

 

But when I associated this with a bit of time data, I am getting this error

 

Filter.PNG

 

Moderator v-qiuyu-msft
Moderator

Re: Some dax functions does not work on PBRS

Hi @dagurkuden,

 

I got information that unfortunately we do not have any information on road map to consider near future. I would request to create comment on forum https://ideas.powerbi.com/forums/265200-power-bi , So that product team will consider the same based on priority and requirement.

 

Regarding the error, please double check if the Allow unrestricted measures in Direct query mode option is enabled. Also as the Values() function return in a column of unique values, please modify the measure like below: 

 

Measure = CALCULATE(SUM('Product'[Amount]),FILTER(ALL('DateVal'),'DateVal'[DateCol] in VALUES(DateVal[DateCol])))

or

Measure 2 = CALCULATE(SUM('Product'[Amount]),ALL(DateVal),VALUES(DateVal[DateCol]))

 

As I tested, it can work fine both in desktop and report server. 

 

q1.PNGq2.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.