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

PowerBI RLS

Hi All,

 

I am new to PowerBI

I have an table with data like email@gmail.com,email2@gmail.com,email3@gmail.com in EmailAddress column.

I have created a Role in  RLS using Username() function like [EmailAddress]=Username().

But while testing the Role in PowerBI Desktop by passing one emailaddress I am not getting the result as expected.

If I pass all the 3 EmailAddress then it is working fine.

Please kindly help me on this is there any function so I can handle this comma seperated data instead of split.

11 REPLIES 11
Anonymous
Not applicable

Hi All,

I am new to PowerBI

I have an table with data like Email@gmail.com,Email2@gmail.com in single cell,I have used Username() function in RLS like [Email]=username().

While Testing the Role in PowerBI Desktop I have passed one of the Email Address like Email@gmail.com I am not getting the expected output . PowerBI is expecting me to pass both the Email address.

kindly please help me on this issue how to get data from comma seperated data is there any  function to do that.

Greg_Deckler
Super User
Super User

You want SEARCH or FIND

 

https://msdn.microsoft.com/en-us/query-bi/dax/search-function-dax

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I wanted DAX to find the EmailAddress of the logged in user from the comma seperated data.

Right, I think the tricky part will be getting this to work with RLS but I would think that something like this would work:

 

SEARCH(USERNAME(),[EmailAddress],0,-1)>0

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg,

 

Is there any way so I can iterate through comma seperated data.

 

I wanted a funtion that iterates through my comma seperated data and when I login with an specific user it needs to filter the roles assigned to the user accordingly.

 

For eg: I am having data like Email@domain.com,Email2@domain.com in single cell.

 

Can You please help me on this.

Anonymous
Not applicable

Hi Greg,

 

Thanks for the Reply.

 

I have used the query given by you to create an Role in RLS.

 

But I am getting error as attached.

Feedback Type:
Frown (Error)

Timestamp:
2018-04-04T11:22:58.5689871Z

Local Time:
2018-04-04T16:52:58.5689871+05:30

Session ID:
1c62272f-fc9a-4269-9dd2-9cca07e8f9a5

Release:
March 2018

Product Version:
2.56.5023.942 (PBIDesktop) (x64)

Error Message:
An error was encountered during the evaluation of the row level security expression defined on table 'UserDetail'. Error message: Row level security expression defined for the table 'UserDetail' is not of type True/False.

OS Version:
Microsoft Windows NT 10.0.16299.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 461308]

Peak Virtual Memory:
34.2 GB

Private Memory:
528 MB

Peak Working Set:
619 MB

IE Version:
11.251.16299.0

User ID:
14338ea1-fde3-4a54-ae82-5696d42acadc

Workbook Package Info:
1* - en-IN, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\HP 240 G6\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1300755002.zip

Performance Trace Logs:
C:\Users\HP 240 G6\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
CustomConnectors
PBI_AdobeAnalytics
PBI_qnaExplore
PBI_variationUIChange
PBI_customVisualsGallery
PBI_canvasTooltips

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:


section Section1;

shared UserDetail = let
    Source = Sql.Database(".", "Test", [Query="SELECT DISTINCT ud.vendorcode,#(lf)  STUFF((SELECT distinct ',' + p1.emailaddress#(lf)         FROM bak_userdetail p1#(lf)         WHERE ud.vendorcode = p1.vendorcode#(lf)            FOR XML PATH(''), TYPE#(lf)            ).value('.', 'NVARCHAR(MAX)')#(lf)        ,1,1,'') email#(lf)FROM bak_userdetail ud;"]),
    #"Added Custom" = Table.AddColumn(Source, "Emailsearch", each [email]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Emailsearch"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Index",{"Index"})
in
    #"Removed Columns1";

shared Vehicle = let
    Source = Sql.Database(".", "Test", [Query="select * from vehicle"])
in
    Source;

Error.PNG

That's what I was afraid of. Perhaps try this:

 

IF(SEARCH(USERNAME(),[EmailAddress],0,-1)=-1,FALSE(),TRUE())

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg,

 

Thank for the reply.

 

I have used the query given by you but I am getting the same error.

 

I have directly used the query in RLS for creating role.

 

Do we have any option to list the comma seperated data in power bi.

 

Is there any alternate solution to handle the data Screenshot (32)_LI.jpg

 

Please help me on this.

 

Thanks

Hi there

I would suggest splitting the data into multiple rows. In doing it in this way it will easily work using RLS




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi,

 

Thanks for reply.

 

I have three roles for example RoleA,RoleB,RoleC.

 

Under RoleA there are 2000 Users.

 

Under RoleB there are 10,000 Users.

 

And Under RoleC there are 15,000 Users.

 

So if I split the data it will be the tedious process Right.

 

Is there any other way to handle Comma seperated data.

 

Thank You.

HI @Anonymous,

 

In fact, even if you achieve dynamic RLS based on username, you still need to assign roles to users.

 

AFAIK, current power bi not support contains batch methods. so I'd like to suggest you submit an idea for add powershell support to assign roles.

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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