CalculateTable with If condition

[Rinku21]

Hi,

I am working on Dynamic row level security.

The requirement is, if the Username() is present in Emp_ad then evaluate table 1 expression else evaluate table 2 expression.

1st expression is:

VAR user_Var=
CALCULATETABLE (
VALUES ( WC_EMPLOYEE_HIERARCHY_DASH_DI[emp_ad] ),
WC_EMPLOYEE_HIERARCHY_DASH_DI[Supervisor_ad] =

UserName()

)
RETURN
EmpIds[emp_ad] IN user_Var

2nd expression is:

var proxy=
CALCULATEtable(
values(Proxy_Table[Proxy_Username]),
Proxy_Table[Username]=UserName()

))
return
proxy_Table[proxy_username] in proxy

Both are working fine individually but I want to add the condition that if username() present in 1st table then evaluate 1st expression if not then 2nd expression.

I tried something like below but its not working. Can anyone please help me on that?

VAR user_Var=
if(CONTAINS(WC_EMPLOYEE_HIERARCHY_DASH_DI,WC_EMPLOYEE_HIERARCHY_DASH_DI[emp_ad] ,

username()
))
,
CALCULATETABLE (
VALUES ( WC_EMPLOYEE_HIERARCHY_DASH_DI[emp_ad] ),
WC_EMPLOYEE_HIERARCHY_DASH_DI[Supervisor_ad] =

username()
)
)
,
if(CONTAINS(Proxy_Table, Proxy_Table[Username] ,
username()
)),

CALCULATETABLE (
VALUES ( proxy_Table[proxy_username] ),
username()
)
)))
RETURN

if((CONTAINS(WC_EMPLOYEE_HIERARCHY_DASH_DI,WC_EMPLOYEE_HIERARCHY_DASH_DI[emp_ad] ,
username()))
), EmpIds[emp_ad] IN user_Var,

proxy_Table[proxy_username] in user_Var)

Thanks,

Rinku

[Anonymous]

Even I got stuck at this step however found the solution while trying. Please use if you are still looking for this solution

you can create a third variable and provide OR condition in filter and call this variable in return 

I started with creating a table. You can replace var vtable1 and vtable2 with yours

Table =

var vtable1 =

SELECTCOLUMNS(FILTER(Sheet1,LOOKUPVALUE('RLS Sample Data'[User_Account_Name],'RLS Sample Data'[Role_Code],"Admin",'RLS Sample Data'[User_Account_Name],"person email id")="person email id"),"__Email",Sheet1[Email])

var vtable2 =

SELECTCOLUMNS(FILTER(Sheet1,[Email] = "person email id"

|| [L5] = "person email id"

|| [L4] = "person email id"

|| [L3] = "person email id"

|| [L2] = "person email id"

|| [L1] = "person email id"

),"__Email",CALCULATE(VALUES(Sheet1[Email]),Sheet1[Email]<>"persona email id"))

This is the variable you have to create which will select either from vtable1 or vtable2

var vtable3 =

SELECTCOLUMNS(FILTER(Sheet1,Sheet1[Email] in vtable1 || Sheet1[Email] in vtable2),"Email",Sheet1[Email])

return

SELECTCOLUMNS(FILTER(Sheet1,Sheet1[Email] in vtable3),"Email",Sheet1[Email])

[Anonymous]

Please mark as solution if this helps you 😊

[amitchandak]

@Rinku21 , I doubt you can username in calculate table. But you should be able to use in table inside measure

[Rinku21]

Hi @amitchandak ,

Thanks for your reply. I added username() just to simplify the expression. I am not using username() instead I am using below expression:

If ( IFERROR(SEARCH("@",USERNAME()),-1) <> -1,

LEFT(SUBSTITUTE(USERNAME(),"@","-"),SEARCH("-",SUBSTITUTE(USERNAME(),"@","-"))-1),

RIGHT(USERNAME(), LEN(USERNAME())-9)