locked
Need help in DAX Expression for RLS Direct query RRS feed

  • Question

  • I have 5 related tables. I have to block users by roles.

    If i enable the 'import' mode below expression is working but not in Direct query mode. Is there any method to convert it so i can use it in direct query mode?

    [id] IN SELECTCOLUMNS (
    FILTER (
        'user_project',
        (RELATED('users'[id]) = VALUE(USERNAME()) && 
        RELATED('roles_groups'[name]) <> "Power User")
       ),  "projects", [project_id]
    )


    Wednesday, June 13, 2018 1:11 PM

Answers

  • Hi rsathish.redcrackle,

    Thanks for your question.

    >>>If i enable the 'import' mode below expression is working but not in Direct query mode. Is there any method to convert it so i can use it in direct query mode?
    If it works in 'import' mode, then it should work in Direct query mode either. The only thing that I can think of is that row-level security features is supported with Analysis Services tabular models at the 1200 and higher compatibility levels. So if you have a Analysis Services tabular models with the compatibility levels lower than 1200, you might find it will not work.

    For more detailed information, please refer to below document about Direct Query Mode:
    https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/directquery-mode-ssas-tabular?view=sql-analysis-services-2017


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 14, 2018 3:28 AM

All replies

  • Hi rsathish.redcrackle,

    Thanks for your question.

    >>>If i enable the 'import' mode below expression is working but not in Direct query mode. Is there any method to convert it so i can use it in direct query mode?
    If it works in 'import' mode, then it should work in Direct query mode either. The only thing that I can think of is that row-level security features is supported with Analysis Services tabular models at the 1200 and higher compatibility levels. So if you have a Analysis Services tabular models with the compatibility levels lower than 1200, you might find it will not work.

    For more detailed information, please refer to below document about Direct Query Mode:
    https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/directquery-mode-ssas-tabular?view=sql-analysis-services-2017


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 14, 2018 3:28 AM
  • Hi willson,

    Thankyou for your reply.

    I created the service in Azure portal. Using 'Visual studio' i have created models with above tables imported from SQL server. Created roles. But still DAX expression is not working. 

    [id] IN SELECTCOLUMNS (
    FILTER (
        'user_project',
        (RELATED('users'[id]) = VALUE(USERNAME()) && 
        RELATED('roles_groups'[name]) <> "Power User")
       ),  "projects", [project_id]
    )
    How should i re-write so it will work for Direct query?


    Monday, June 25, 2018 5:47 PM
  • Hi rsathish.redcrackle,

    Thanks for your response.

    According to your description, According to your description, your problem is more related to Azure Analysis Services. Since our forum is discussing Power Pivot issue, to solve your question more efficiently, please post your question in Azure Analysis Services forum, you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 26, 2018 2:45 AM