Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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

    • Anonymous's avatar
      Anonymous
      Not 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)

       

       

  • Anonymous's avatar
    Anonymous
    Not 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 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's avatar
      Anonymous
      Not applicable

      Please mark as solution if this helps you ğŸ˜Š