Using a measure for filtering, how do I return all the columns a role can see?

[Adehmar]

Hello, I have multiple roles, and some users can have multiple of them. Currently, I can view (in the visuals) the table and columns only if the user has one role. If it has multiple, I get the error "A table of multiple values was supplied where a single value was expected".

What I want is for users that have "n" roles, to be shown everything that their roles allow (logically). It's a one to many relationship, as one user can have multiple roles. I'm doing this with RLS.

This is the current expression I have, which works only if the person has one role:

IF(
  NOT(
    ISBLANK(
      LOOKUPVALUE(
                  'Mail Agrupaciones'[Agrupación],'Mail Agrupaciones'[Mail],USERPRINCIPALNAME()
                 )
           )
     ),[Agrupación Nombre] 
= 
LOOKUPVALUE(
            'Mail Agrupaciones'[Agrupación],'Mail Agrupaciones'[Mail],USERPRINCIPALNAME()
           ), 1=1 
)

Thank you.

[WinterMist]

@Adehmar 

1) Is the expression just a measure in your PBIX?

2) How are you displaying the output of the expression in a visual?

3) Could you provide a small test PBIX to show a small set of sample data, and how the expression is returning 1 value correctly?  If a PBIX is not possible, can you at least share screenshots of a few sample data rows along with how you are using the expression (measure)?

NOTE: Please show corresponding rows in both:

A) 'Mail Agrupaciones' table (AND)

B) Your front-end visual output where the expression/measure is used.

Regards,

Nathan

[WinterMist]

Hello @Adehmar - 

1) If you want users that have "n" roles to be shown everything their roles allow, this should work without the expression.

For example:

- Here is a test table "Colors".

- Here is a test table called "Groups".  Just as you say, each user can have "n" number of Groups. 

   - User chris.m has 3 Groups.

   - User eric.c has 2 Groups.

   - User chris.p has 1 Group.

- RLS Roles are defined as follows 

- By viewing as a person with all 3 roles (Color 1, Color 2 & Color 3), such as chris.m, all 3 roles are combined and the user can only see the values across the 3 roles.

- By viewing as a person with only 2 roles (Color 1 & Color 2), such as eric.c, only those 2 roles are combined and the user can only see the values across those 2 roles.

2) LOOKUPVALUE function can only return a single value.  For this reason, it throws the error, because you are trying to return a table of values for a single lookup value.  For example, if I try to lookup chris.m, it will not return a single value for Group, but a table of values (Color 1, Color 2, Color 3) for Group.  But again, the expression is not needed to do this as RLS will already combine the values across the multiple roles assigned to a given user.

Hopefully this is helpful to you.

Regards,

Nathan

[Adehmar]

Thank you, this is much more helpful than what I had. However, currently I have 52 roles, and there is the need to add more as time goes by, so adding so many by hand on the roles section is not efficient, I think. Currently I have just one role, and the filtering is done on the tables.

I understand that about point 2. I guess, put it simply, what I'm trying to do is replace LOOKUPVALUE with something that returns multiple values instead of just one. If you could guide me on that it'd be great, for some reason I can't figure it out.

Nonetheless, this answer if helpful for another project. Thank you for your time.

[Adehmar · Accepted solution]

I managed to make the solution with the following:

IF (
    COUNTX (
        FILTER ( 'Mail Agrupaciones', 'Mail Agrupaciones'[Mail] = USERPRINCIPALNAME () ),
        'Mail Agrupaciones'[Mail]
    ) > 0,
    VAR VAR_TABLA = CALCULATETABLE('Mail Agrupaciones', 'Mail Agrupaciones'[Mail] = USERPRINCIPALNAME())
    RETURN
    [Agrupación Nombre] IN SUMMARIZE(VAR_TABLA,'Mail Agrupaciones'[Agrupación])
    ,
    1 = 1
)

Thank you.