Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MathiasChd
Frequent Visitor

RLS Dynamic with conditions from diferent tables

Hello everybody:

 

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

 

FechaProductoOperaciónCDG_SGMNTCantidadGTEIDGTE_NMBR
30/6/202111428681P6275Hector Alonso
30/6/202111428698P6075Hector Alonso
30/6/202111428708P9075Hector Alonso
30/6/202111428722P3675Hector Alonso
30/6/202111428739P1375Hector Alonso
30/6/202111428746P31154Victor Caballero
30/6/202111428753P3275Hector Alonso
30/6/202111428784P95154Victor Caballero
30/6/202111428863P9175Hector Alonso
30/6/202111428887P7775Hector Alonso

 

A dimension table like this

CDG_SGMNTDescripcionCategoria
CCorporacionesMayorista
GDistribuidoresMayorista
YEmpresas PequeñasMinorista
SEmpresas MedianasMinorista
QSegmento VipMinorista
ARevendedoresMinorista
BAgriculturaMayorista
KRevendedoresMinorista
PRevendedoresMinorista
VAgriculturaMayorista
TFinanzasFinanzas
XInversionesOTROS
ISegurosOTROS

 

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

MathiasChd_0-1626407809804.png

 

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

NOMBREGTEIDNOMBRE CATEGORIAID PerfilEmail
Jorge Espinola  3jorge.espinola@empresa.com
Martin Gonzales  2martin.gonzales@empresa.com
Maria Diaz  1maria.diaz@empresa.com
Hector Paredes  4hector.paredes@empresa.com
Ruben Alfonso  6ruben.alfonso@empresa.com
Lucas Espiritu  12LAPTOP-SR63JAC4\Mathias
Juliano Bosselli  6juliano.bosselli@empresa.com
Paolo Guerrero  12paolo.guerrero@empresa.com
Emilio Rojo  6emilio.rojo@empresa.com
Jorge Ocampos53Agricultura15jorge.ocampos@empresa.com
Josefina Maciel144Distribuidores16josefina.maciel@empresa.com
Gustavo Ayala147Corporaciones17gustavo.ayala@empresa.com
Sergio Gomez29Empresas Pequeñas18sergio.gomez@empresa.com
Anthonio Ojeda9Segmento VIP19anthonio.ojeda@empresa.com
Esteban Rios126Empresas Medianas20esteban.rios@empresa.com
Rolando Moreira175Segmento VIP21rolando.moreira@empresa.com
Hector Alonso75Revendedores22hector.alonso@empresa.com
Victor Caballero154Revendedores23victor.caballero@empresa.com
Herniesto Caballero6Finanzas24herniesto.caballero@empresa.com

 

TablaPerfil

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

 

TablaInclude

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

 

TablaGte

ID PerfilGTEIDGTE_NMBR
1553Jorge Ocampos
16144Josefina Maciel
17147Gustavo Ayala
1829Sergio Gomez
199Anthonio Ojeda
20126Esteban Rios
21175Rolando Moreira
2275Hector Alonso
23154Victor Caballero
246Herniesto 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 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

yingyinr_0-1626679633712.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
MathiasChd
Frequent Visitor

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
Community Support
Community Support

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

yingyinr_0-1626679633712.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, this is the solution I was needing!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors