Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abipanoor
Regular Visitor

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

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.
 

Fold exp error.PNG

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

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

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.