Filter Dinamic for USER

[dlagos]

I have this dax but don´t work by user... how can a fix this??

    MEASURE '02-Cal Real'[01-Real Dinamico] =

VAR Real = '02-Cal Real'[01-Real]

VAR Usuario = USERPRINCIPALNAME()

VAR UsuarioActual = FILTER('Maestro RLS',

'Maestro RLS'[Correo electrónico] = Usuario)

VAR CCActual = SELECTCOLUMNS(UsuarioActual,"@CC",

'Maestro RLS'[CC])

RETURN

CALCULATE(Real,'Centro de Costo'[DEPENDENCIA (Oracle)] IN CCActual)

EVALUATE

    SUMMARIZECOLUMNS('01-Calendario'[Año],'01-Calendario'[Nombre Corto Mes],'Centro de Costo'[DEPENDENCIA (Oracle)],

        "01-Real", '02-Cal Real'[01-Real Dinamico]

    )

[Anonymous]

Hi @dlagos 

It looks like there are a few issues with the DAX code you provided. Here are some steps you can take to fix it:

Wrap USERPRINCIPALNAME() in a CALCULATE function, this ensures that the function is used within a row context. Use CALCULATETABLE for FILTER and SELECTCOLUMNS, this helps create a row context for these functions. Ensure the IN operator is used within a CALCULATE function, this also helps create a row context.

MEASURE '02-Cal Real'[01-Real Dinamico] =
VAR Real = '02-Cal Real'[01-Real]
VAR Usuario = CALCULATE(USERPRINCIPALNAME())
VAR UsuarioActual = CALCULATETABLE(
    FILTER('Maestro RLS', 'Maestro RLS'[Correo electrónico] = Usuario)
)
VAR CCActual = CALCULATETABLE(
    SELECTCOLUMNS(UsuarioActual, "@CC", 'Maestro RLS'[CC])
)
RETURN
CALCULATE(Real, 'Centro de Costo'[DEPENDENCIA (Oracle)] IN CCActual)

EVALUATE
SUMMARIZECOLUMNS(
    '01-Calendario'[Año],
    '01-Calendario'[Nombre Corto Mes],
    'Centro de Costo'[DEPENDENCIA (Oracle)],
    "01-Real", '02-Cal Real'[01-Real Dinamico]
)

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dlagos]

it doesn't work, but is really good idea  

[rajendraongole1]

Hi @dlagos  - Improvised measure as below:

MEASURE '02-Cal Real'[01-Real Dinamico] =
VAR Real = '02-Cal Real'[01-Real]
VAR Usuario = USERPRINCIPALNAME()

-- Filter the Maestro RLS table to get the current user's row
VAR UsuarioActual = FILTER('Maestro RLS', 'Maestro RLS'[Correo electrónico] = Usuario)

-- Get the list of CC values for the current user
VAR CCActual = SELECTCOLUMNS(UsuarioActual, "@CC", 'Maestro RLS'[CC])

-- Check if CCActual has values and apply the filter dynamically
RETURN
IF (
ISBLANK(CCActual),
BLANK(),
CALCULATE(
Real,
'Centro de Costo'[DEPENDENCIA (Oracle)] IN VALUES(CCActual[@CC])
)
)

Ensure that row-level security (RLS) is properly configured in Power BI and the Maestro RLS table is included in the model.

Hope this helps.

[dlagos]

Now i have this error