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 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.
 

Need Help
Tips and Tricks

3 Replies

  • 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