Forum Discussion
CalculateTable with If condition
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
4 Replies
- amitchandakSuper User
Anonymous , I doubt you can username in calculate table. But you should be able to use in table inside measure
- AnonymousNot applicable
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)
- AnonymousNot applicable
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 yoursTable =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])returnSELECTCOLUMNS(FILTER(Sheet1,Sheet1[Email] in vtable3),"Email",Sheet1[Email])- AnonymousNot applicable
Please mark as solution if this helps you 😊