We couldn't fold the expression to the datasource error while using dax with direct query

[abipanoor]

Hi All, 

I am using direct query mode in a report and also having a set of fields which are using dax formulas in the report. When I apply the data security I am getting the below error. The report is failed to load. But if I am not using data security I am able to see data in my report.

Here is the DAX used, 

Gross Profit % To Date DAX =

IF(COUNTROWS(VW_CS_SUMMARY) > 0,

    IF(ISFILTERED(VW_PERIOD_DIM[PERIOD LABEL]),

        AVERAGE(VW_CS_SUMMARY[GROSS_PROFIT_PERCENT_TO_DATE]),

        DIVIDE(SUM(VW_CS_CONTRACTSTATUSBYJOBAGG[GROSS_PROFIT]), SUM(VW_CS_CONTRACTSTATUSBYJOBAGG[Revised_Contract]))

    ),

    BLANK())

I have around 10-12 fields created using similar formula..
Is there a workaround to fix this issue or any other alternative options? Any help would be appreciated.
 

[selimovd · Accepted solution]

Hey @abipanoor ,

the problem are the function COUNTROWS and ISFILTERED. Both do not work in RLS:

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

See the official documentation for more details and also for the infor if the functions work with DQ:

COUNTROWS function (DAX) - DAX | Microsoft Learn

ISFILTERED function (DAX) - DAX | Microsoft Learn

So you have to find a workaround that also works with RLS or you push the security to the data source.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[abipanoor]

Thanks Denis. This is really helpful for us to understand the root cause. will look into it. 

[selimovd]

Hey @abipanoor ,

that's one of the disadvantage of direct query. Many DAX functions won't work properly.

Good luck with the new approach!

Don't forget to mark the post as solution. Like this the next person who is stumbling across this post knows if the answer added a value to figure out the problem.

Best regards

Denis