Table function instead table or RLS...

[sekic]

In query editor I try to call table function which have one varchar input parameter.

When parameter is constant, return rows are fine.

In SQL Profiler I can see that Power BI call comand such as "select ... from function('abc@xyz.com')"

But when try to use USERPRINCIPALNAME() as value (either as USERPRINCIPALNAME() or as Text.From(USERPRINCIPALNAME())) response is invalid, because Power BI call is "select ... from function('USERPRINCIPALNAME()')" or

"select ... from function('Text.From(USERPRINCIPALNAME())) ')".

It use constant 'USERPRINCIPALNAME()' instead of value of dax function USERPRINCIPALNAME().

How to send real value to table function?

[v-qiuyu-msft · Accepted solution]

Hi @sekic,

USERPRINCIPALNAME() is a DAX function, it's not available in Power Query. When you connect to SQL Server database, Power BI use Power Query then generated T-SQL to get data. The USERPRINCIPALNAME() will be treated as a text than passed into table function.

In your scenario, please try to use the RLS on Power BI side. See: Row-level security (RLS) with Power BI.

Best Regards,
Qiuyun Yu

[sekic]

Ok, is there any data, except constant,  which can be send from Power BI to SQL, in query editor, as argument to function?

[sekic]

In query editor I try to call table function which have one varchar input parameter.

When parameter is constant, return rows are fine.

In SQL Profiler I can see that Power BI call comand such as "select ... from function('abc@xyz.com')"

But when try to use USERPRINCIPALNAME() as value (either as USERPRINCIPALNAME() or as Text.From(USERPRINCIPALNAME())) response is invalid, because Power BI call is "select ... from function('USERPRINCIPALNAME()')" or

"select ... from function('Text.From(USERPRINCIPALNAME())) ')".

It use constant 'USERPRINCIPALNAME()' instead of value of dax function USERPRINCIPALNAME().

How to send real value to table function?