Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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
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
Not applicable

Please mark as solution if this helps you 😊

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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)

 

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.