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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sekic
Helper I
Helper I

Table function instead table or RLS...

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?

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

sekic
Helper I
Helper I

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?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors