cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rinku21
Helper II
Helper II

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 4
nmukesh
New Member

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])

Please mark as solution if this helps you 😊

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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)

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.