RLS Dynamic with conditions from diferent tables

[MathiasChd]

Hello everybody:

I have the table facts like this (I share the link of the complete file of 100.000 rows)

Fecha	Producto	Operación	CDG_SGMNT	Cantidad	GTEID	GTE_NMBR
30/6/2021	1	1428681	P	62	75	Hector Alonso
30/6/2021	1	1428698	P	60	75	Hector Alonso
30/6/2021	1	1428708	P	90	75	Hector Alonso
30/6/2021	1	1428722	P	36	75	Hector Alonso
30/6/2021	1	1428739	P	13	75	Hector Alonso
30/6/2021	1	1428746	P	31	154	Victor Caballero
30/6/2021	1	1428753	P	32	75	Hector Alonso
30/6/2021	1	1428784	P	95	154	Victor Caballero
30/6/2021	1	1428863	P	91	75	Hector Alonso
30/6/2021	1	1428887	P	77	75	Hector Alonso

A dimension table like this

CDG_SGMNT	Descripcion	Categoria
C	Corporaciones	Mayorista
G	Distribuidores	Mayorista
Y	Empresas Pequeñas	Minorista
S	Empresas Medianas	Minorista
Q	Segmento Vip	Minorista
A	Revendedores	Minorista
B	Agricultura	Mayorista
K	Revendedores	Minorista
P	Revendedores	Minorista
V	Agricultura	Mayorista
T	Finanzas	Finanzas
X	Inversiones	OTROS
I	Seguros	OTROS

I have the following data model, on the left side is the model data and on the right the tables for RLS

And I need to apply RLS under the following conditions.

If the USERPRINCIPALNAME() who login is the same as the "GTEID" he can only view the data corresponding to "GTEID",

otherwise he can view according to the profile granted in this table

Currently my pbix. works for the second condition but not for the first where 'HECHOS'[GTEID] = 'USERS'[GTEID]

The tables for RLS are

Users

NOMBRE	GTEID	NOMBRE CATEGORIA	ID Perfil	Email
Jorge Espinola			3	jorge.espinola@empresa.com
Martin Gonzales			2	martin.gonzales@empresa.com
Maria Diaz			1	maria.diaz@empresa.com
Hector Paredes			4	hector.paredes@empresa.com
Ruben Alfonso			6	ruben.alfonso@empresa.com
Lucas Espiritu			12	LAPTOP-SR63JAC4\Mathias
Juliano Bosselli			6	juliano.bosselli@empresa.com
Paolo Guerrero			12	paolo.guerrero@empresa.com
Emilio Rojo			6	emilio.rojo@empresa.com
Jorge Ocampos	53	Agricultura	15	jorge.ocampos@empresa.com
Josefina Maciel	144	Distribuidores	16	josefina.maciel@empresa.com
Gustavo Ayala	147	Corporaciones	17	gustavo.ayala@empresa.com
Sergio Gomez	29	Empresas Pequeñas	18	sergio.gomez@empresa.com
Anthonio Ojeda	9	Segmento VIP	19	anthonio.ojeda@empresa.com
Esteban Rios	126	Empresas Medianas	20	esteban.rios@empresa.com
Rolando Moreira	175	Segmento VIP	21	rolando.moreira@empresa.com
Hector Alonso	75	Revendedores	22	hector.alonso@empresa.com
Victor Caballero	154	Revendedores	23	victor.caballero@empresa.com
Herniesto Caballero	6	Finanzas	24	herniesto.caballero@empresa.com

TablaPerfil

ID	Perfil
1	Total
2	Mayorista
3	Minorista
4	Finanzas
5	Otros
6	Agricultura
7	Revendedores
8	Empresas Pequeñas
9	Distribuidores
10	Segmento Vip
11	Empresas Medianas
12	Corporaciones
13	Inversiones
14	Seguros

TablaInclude

ID Perfil	Nombre
2	Agricultura
2	Distribuidores
2	Corporaciones
3	Revendedores
3	Empresas Pequeñas
3	Segmento Vip
3	Empresas Medianas
4	Finanzas
5	OTROS
6	Agricultura
7	Revendedores
8	Empresas Pequeñas
9	Distribuidores
10	Segmento Vip
11	Empresas Medianas
12	Corporaciones
13	Inversiones
14	Seguros

TablaGte

ID Perfil	GTEID	GTE_NMBR
15	53	Jorge Ocampos
16	144	Josefina Maciel
17	147	Gustavo Ayala
18	29	Sergio Gomez
19	9	Anthonio Ojeda
20	126	Esteban Rios
21	175	Rolando Moreira
22	75	Hector Alonso
23	154	Victor Caballero
24	6	Herniesto Caballero

I need a single dynamic role to apply to the user group, since there are more than 100 users and assigning roles manually would be complicated.

I thank you in advance for your help 

[MathiasChd]

Here is another solution that I found to the problem, adding the profiles of [GTEID] to the 'TablaPerfil' and doing the following in the role of Table "Hechos":

VAR ID_Perfil = 
SELECTCOLUMNS(
FILTER( 'Users', 'Users'[Email]=
USERPRINCIPALNAME()),
"ID", [ID PERFIL] )

VAR _uGTEID = 
SELECTCOLUMNS(
FILTER(
'TablaGte',
'TablaGte'[ID Perfil] in
ID_Perfil
),
"Tabla Gte", 'TablaGte'[GTEID])

RETURN

IF(
COUNTROWS( _uGTEID ) >0 ,
[GTEID] IN
_uGTEID ,
true)

Thanks for the support and the prompt reply.

[v-yiruan-msft · Accepted solution]

Hi @MathiasChd ，

Please add the following filter for the table Hechos and check whether that can achieve your requirement:

[GTEID] =
VAR _userp = USERPRINCIPALNAME()
VAR _uGETID =
    CALCULATE (
        MAX ( 'Users'[GTEID] ),
        FILTER ( 'Users', 'Users'[Email] = _userp )
    )
RETURN
    _uGETID

Best Regards

[MathiasChd]

Thanks, this is the solution I was needing!!