Forum Discussion

abipanoor's avatar
abipanoor
Regular Visitor
3 years ago
Solved

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 rep...
Need Help
Tips and Tricks
  • selimovd's avatar
    selimovd
    3 years ago

    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

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
3 years ago

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's avatar
abipanoor
Regular Visitor
3 years ago

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

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    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