none
Row Filter on SSAS Tabular Roles RRS feed

  • Question

  • Hi, 

    I'm trying to setup row filtering on certain roles I have in one of my SSAS cubes.

    I have a table containing usernames for users who's access should be limited.

    If the user is present in that table it should return all rows where his username is present in the field SSO (this works).If the user is not in that table he should be able to see everything (so all rows should be visible, this doesn't work).

    This table is called SalesRepSecurity.

    I have the following DAX expression:

    =IF(COUNTROWS(FILTER(SalesRepSecurity, PATHCONTAINS(SalesRepSecurity[SSO], USERNAME())))=0,
    TRUE(), 
    PATHCONTAINS(SalesRepSecurity[SSO], USERNAME()))

    For the TRUE() part I tried with TRUE, TRUE(), 1=1, PATHCONTAINS(SalesRepSecurity[x], "1") having a column "x" with all "1" values but no success.

    When the user is in the table it works but when the user is not present no rows are returned.

    I tried this with DaxStudio and the result returned is as expected.

    What am I doing wrong? 

    Thanks


    • Edited by Alex_2308 Monday, December 2, 2019 3:39 PM
    Monday, December 2, 2019 3:35 PM

Answers

  • Hi Will, 

    Thanks for your feedback.

    I will try to reformat my table to way you describe it. Shouldn't be a problem.

    Although how would I work around the fact the if the user is not present in the table it should see everything? Or is there no way around this and I need all users in that table?

    Best regards,

    Alex

    Hi Alex,

    This requirement could be achieved by using LOOKUPVALUE function. Because there is a parameter called alternateResult, which could be specified when there is no matched value in related table.

    LOOKUPVALUE

    Best Regards,

    Will


    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.

    • Marked as answer by Alex_2308 Thursday, December 5, 2019 10:52 AM
    Thursday, December 5, 2019 7:22 AM

All replies

  • Hi Alex,

    Thanks for your post.

    In my opinion, row filters should be condition expression. Based on this, the DAX statement could be written like this.

    SalesRepSecurity[SSO]=IF(
       COUNTROWS(FILTER(SalesRepSecurity, PATHCONTAINS(SalesRepSecurity[SSO], USERNAME())))=0,SalesRepSecurity[SSO],USERNAME())

    Best Regards,

    Will



    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, December 3, 2019 2:15 AM
  • Hi Will,

    Thanks for your feedback.

    I think we are talking about two different things.

    So I'm trying to setup row filters in the role manager of one of the cubes. I have different roles and in the row filter section, next to the table SalesRepSecurity is where I'm setting up my expression. Where it says "Specify DAX expressions that return Boolean values. Only rows that match the specified filters are visible to users in this role".

    I tried your proposal but the results are not what I expect. If I just take the part after the "=" it tells me "The DAX expression must return a boolean value".

    The table SalesRepSecurity is related to different fact tables. When this table SalesRepSecurity is filtered, it filters also the fact tables to limit the access.

    Any other ideas?

    Thanks,

    Alex

    Tuesday, December 3, 2019 8:48 AM
  • Hi Will,

    Thanks for your feedback.

    I think we are talking about two different things.

    So I'm trying to setup row filters in the role manager of one of the cubes. I have different roles and in the row filter section, next to the table SalesRepSecurity is where I'm setting up my expression. Where it says "Specify DAX expressions that return Boolean values. Only rows that match the specified filters are visible to users in this role".

    I tried your proposal but the results are not what I expect. If I just take the part after the "=" it tells me "The DAX expression must return a boolean value".

    The table SalesRepSecurity is related to different fact tables. When this table SalesRepSecurity is filtered, it filters also the fact tables to limit the access.

    Any other ideas?

    Thanks,

    Alex

    Hi Alex,

    Thanks for your reply.

    For other related fact tables, you could use LOOKUPVALUE function to combine the condition expression. For more details, please refer:

    Create the tabular model with facts and dimension tables

    Best Regards,

    Will


    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, December 3, 2019 9:10 AM
  • Hi Will,

    Thanks for the link and your feedback, but I'm not sure it will help me in my case.

    Just to clarify. I have a table similar to this:

    UserSSO    SSO   
    1 1|3|4
    2 2|3|4
    3 3|4
    4 4

    The idea is if user 3 logs in he can see all rows where he's present in SSO. So the first 3 rows. Since he is the manager of user 1 and 2.

    So far my DAX works for this scenario.

    Now this table only contain certain users (those that have limitations).

    If user 5 would log in, since he is not in the table he should be able to see all rows.

    This is what I'm struggling with. When I test my DAX in DAXStudio, all rows are returned as expected.
    When I try to set it up in my row filter no rows are returned. I tried your proposed solution but no rows get returned.

    Best regards,

    Alex



    • Edited by Alex_2308 Tuesday, December 3, 2019 11:09 AM
    Tuesday, December 3, 2019 10:57 AM
  • Hi Alex,

    Thanks for your detailed description.

    In fact, it could be done by using my solution. Due to that data storage format is wrong in your table, it goes against table design rule. If possible, you need to turn the table records to this format before loading into tabular model:( it is easy to achieve by using SQL statement)

    UserSSO   SSO 

    1         1

    1         3

    1         4

    2         2

    2         3

    2         4

    3         3

    3         4

    4         4

    Otherwise, it would increase the burden of writing complex DAX statements. At the same time, it would hard to maintain such complex statement.

    Best Regards,

    Will


    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.

    Wednesday, December 4, 2019 2:42 AM
  • Hi Will, 

    Thanks for your feedback.

    I will try to reformat my table to way you describe it. Shouldn't be a problem.

    Although how would I work around the fact the if the user is not present in the table it should see everything? Or is there no way around this and I need all users in that table?

    Best regards,

    Alex

    Wednesday, December 4, 2019 8:24 AM
  • Hi Will, 

    Thanks for your feedback.

    I will try to reformat my table to way you describe it. Shouldn't be a problem.

    Although how would I work around the fact the if the user is not present in the table it should see everything? Or is there no way around this and I need all users in that table?

    Best regards,

    Alex

    Hi Alex,

    This requirement could be achieved by using LOOKUPVALUE function. Because there is a parameter called alternateResult, which could be specified when there is no matched value in related table.

    LOOKUPVALUE

    Best Regards,

    Will


    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.

    • Marked as answer by Alex_2308 Thursday, December 5, 2019 10:52 AM
    Thursday, December 5, 2019 7:22 AM
  • Thanks for the support Will. It finally works :D

    The LOOKUPVALUE helped but also figuring out after 2 days that I had a wrong test scenario.

    Best regards,

    Alex

    Thursday, December 5, 2019 10:54 AM