RLS with conditional tables

[naninamu]

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

[naninamu]

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.

[naninamu]

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

[Anonymous · Accepted solution]

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.