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 to reference to find out the Area associated with that User. There are 2 such tables, which then act to filter a main table which ultimately gives the Av$ associated to that Area.

 

So in my below simplified example, if User 100 is using the model, it will jump to 'Table A', identify 'Nth' as the Area, and then filter Nth on the main table to show $100 as the final result.

 

I can't quite work out how to do this.... it's the bit with having to pick either Table A or Table B that is confusing me.

 

If anyone can steer me in the right direction, would be much appreciated.

 

Thanks - Andrew

 

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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 so much for this! I will have a go at implementing it (to the slightly more complicated real life situation) and see how we go. 

     

    Cheers, Andrew

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