Forum Discussion

naninamu's avatar
naninamu
Helper IV
3 years ago
Solved

RLS with conditional tables

Hello - was helping someone could help me as I attempt to implement RLS on a model I've inherited.   Simply put, the User is in a table with their ID and another column which specifics which table ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi naninamu ,

    Please try to create a role with below dax formula:

    [Area] =
VAR _id =
    USERPRINCIPALNAME ()
VAR a =
    CALCULATE ( MAX ( 'User'[Table] ), CONTAINSSTRING ( 'User'[ID], _id ) )
VAR b =
    CALCULATE ( MAX ( 'Table A'[Area] ), CONTAINSSTRING ( 'Table A'[ID], _id ) )
VAR c =
    CALCULATE ( MAX ( 'Table B'[Area] ), CONTAINSSTRING ( 'Table B'[ID], _id ) )
VAR val =
    SWITCH ( a, "A", b, "B", c )
RETURN
    val

    add a table visual with Table fields:

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

naninamu's avatar
naninamu
Helper IV
3 years ago

Thank you for the solution you have provided. I have had a look and think I understand it, but had some follow up questions:

 

1) I have realised I have over simplified my model.  A User can actually appear in BOTH Tables A and B, but with different Areas. That's why the initial selection of Table A or B is so important. I believe with your code this would be an issue? Can the code be adapted to handle this?

 

2) I'm thinking I can't even set my data model up to reflect my situation as it will have an ambiguous path. The only way around it would be to have an inactive relationship (so if User to Table A is active, User to Table B is inactive -  which is somehow activated when required. My understanding is this is not possible to acheive using DAX in RLS. Am I correct in this understanding?

 

Thanks in advance.