cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Regular Visitor

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.

4 REPLIES 4
Impactful Individual

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

Impactful Individual

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

Regular Visitor

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.

Regular Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors