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
Want To Learn Power BI
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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors