Lookup table in combination with searching a text string

[skocheta]

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)

[skocheta · Accepted 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.

[v-frfei-msft]

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

[skocheta]

Hello Frank
Thanks for your response.
The reason for using concatenatex was so as to loop over all accountlist and search in the oppacc table.
The issue however is that the formula (both original as well as proposed solution),does not recognise AccountList table.

Is the variable supposed to be used differently? Or var can't be uaed here at all , what are the alternative options


Thanks

[v-frfei-msft]

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