Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic RLS - keep rows in fact table when either column A or column B matches condition

Hello,

 

I'd like to set up dynamic RLS in a way that rows in the fact table are kept when either 'fact'[Team_assessment] or 'fact'[Team_implementation] matches 'RLS'[Team_coordinator].

 

Table 'fact'

Id_IdeaTeam_assessmentTeam_implementation
1redred
2blueblue
3redblue

 

Table 'RLS'

id_coordinatorEmail_coordinatorTeam_coordinator
1[email protected]red
2[email protected]blue
3[email protected]red

 

Expected Result

id_IdeaTeam_assessmentTeam_implementationis seen by 
1redred1, 3
2blueblue2
3redblue1, 2, 3

 

This would be easy if only 'fact'[Team_assessment] or 'fact'[Team_implementation] had to be filtered by 'RLS', since basic dynamic RLS would be sufficient.

I already created a role 'role 1' Email_coordinator = USERPRINCIPALNAME() and a one column bridge table 'bridge' containing unique values of 'RLS'[Team_Coordinator]. 

 

Could someone provide me a link or a suggestion on how to go about this problem?

Many thanks in advance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please have a try.

    [Id_Idea] in CALCULATETABLE(VALUES('Table'[Id_Idea]),FILTER(ALL('Table'),'Table'[Team_assessment]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
||'Table'[Team_implementation]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
))

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Create a measure.

    Measure = 
var cur_user=USERPRINCIPALNAME()
var td=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=cur_user)
var _str=SELECTEDVALUE('Table'[Team_assessment])&SELECTEDVALUE('Table'[Team_implementation])
return
IF(CONTAINSSTRING(_str,td),1)

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi v-polly-msft,

       

      Thank you for taking your time and the detailed answer.

       

      This workaround using a visual-level filter works perfectly, but only for a table visual. Unfortunately I have lots of measures in my report which are based on the fact table. Is there a way to implement your logic as a table filter dax expression in the Manage Roles tab? 

       

      Best regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    [Id_Idea] in CALCULATETABLE(VALUES('Table'[Id_Idea]),FILTER(ALL('Table'),'Table'[Team_assessment]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
||'Table'[Team_implementation]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
))

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi v-polly-msft

       

      This is exactly what I was looking for. Thank you so much.

       

      Best regards and many thx