Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I am working on Row level security and have to dynamically filter a table based on the user's access to another table, Which gives me a list of Accounts he may have access to.
e.g.
Here is what I am trying to do:
/*Get list of accounts the user has access to by seraching for the user id in the team hierarchy table (which maintains all accounts the user or his reporting users have access to)*/
var AccountList = SELECTCOLUMNS(
FILTER(
TEAM,
SEARCH(
IF(ISBLANK(
CALCULATE(
VALUES('systemusers'[systemuserid])
,systemusers
,'systemusers'[internalemailaddress]=USERPRINCIPALNAME()
)
),"-----"
,CALCULATE(
VALUES('systemusers'[systemuserid])
,systemusers
,'systemusers'[internalemailaddress]=USERPRINCIPALNAME()
)
)
,TEAM[TEAMUserHierarchy],1,0)>0
)
, "Customer", Team[Customer Name])
/*What we should get above is a Table variable with column name Customer*/
/*Below, I am trying to use the table defined above as a parameter to Concatenate function, but it does not recognize the Variable. Please suggest if I am using the variable in wrong way, This is the first time I am using variable.*/
var result = CONCATENATEX(AccountList,SEARCH(FIRSTNONBLANK(AccountList[Customer],1),OppAcc[Design_Customer],0,1))
//Pattern used from https://www.youtube.com/watch?v=_bdHe3z_fVU
RETURN IF(result>0,1,0)
Solved! Go to Solution.
Unfortunately none of the proposed solutions worked, I finally ended up changing the approach by adding a reference column to the original table.
Thanks for all your help.
Unfortunately none of the proposed solutions worked, I finally ended up changing the approach by adding a reference column to the original table.
Thanks for all your help.
Hi @skocheta,
As CONCATENATEX function will return a text string, so you cannot use that like the way as yours, you can refer to the online document. Here I suggest you to update your formula like this to have a try.
Column = var result = SEARCH(FIRSTNONBLANK(AccountList[Customer],1),OppAcc[Design_Customer],,0) return if(result>0,1,0)
Regards,
Frank
Hi @skocheta,
Actually it is the CONCATENATEX function. It will return a string text, in your formula, it is unreasonable to make Comparison with 0.
RETURN IF(result>0,1,0)
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |