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

DAX Expression For Role Level Security Using DirectQuery

I've written a DAX expression I'd like to use in Role Level Security using DirectQuery.  My understanding is that some DAX functions will be limited.  (Apologies for the unformatted DAX code in advance)

 

I'm receiving an error that the syntax for CONTAINS is incorrect.

 

IF((CONTAINS(Person,[Alias],USERPRINCIPALNAME(),IF(LOOKUPVALUE(Person,Person[isSalesPerson],1)),USERPRINCIPALNAME(),"N/A"),BLANK()))

 

 dax.png

 

What I am trying to accomplish is something like this:

 

 

= IF(CONTAINS(Person,Person[Alias], USERPRINCIPALNAME() – Is this person in the Person table?

 

                                -- Yes

 

, IF(CONTAINS(Person, Person[IsSalesPerson], 1),  -- Is this a Sales Rep?

               

                             -- If Yes, return:

 

USERPRINCIPALNAME()

 

-- If No, then return value:

 

, ‘N/A’),

                                -- No, Then return value:

 

BLANK()) – No access

 

My data model is here:

 

dax2.png

 

Does anyone have a thought on this solution?  Thank you in advance!

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

I guess this will work, at least with my simple test data

check Role = 
IF(CONTAINS('person',person[Alias],USERPRINCIPALNAME())
    ,IF(
        LOOKUPVALUE(person[isSalesPerson],person[Alias],USERPRINCIPALNAME())=1
        ,USERPRINCIPALNAME()
        ,"N/A"
    )
    ,BLANK()
)


If USERPRINCIPALNAME() is not in the column [Alias] the function returns BLANK
If USERPRINCIPALNAME() is a salesperson it returns USERPRINCIPALNAME() otherwise "N/A

Please be aware that LOOKUPVALUE works when only one row matches the condition.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

11 REPLIES 11
ManishSingh
New Member

Hi IAll,

 

am trying to use Search or ContainsString function in Direct Query mode but getting the same error that these functions are not supported in direct query model. However in the list of optimized dax functions they are listed.

Also, there is no option in the latest power bi desktop version to allow unrestricted measures in direct query mode.

Please help as this issue has become a major bottleneck for us.

Anonymous
Not applicable

I have an scenario where 2 authorisation tables for Company level access & Individual access, some users will be in both Company & Individual Roles (separate tables) . I have 2 roles for Company & Individuals and each role is filtering the authorisation table using key columns wrt transaction table. when i test the user who as access to both, the rls overides the condition. so please advise how to overcome this situation

TomMartens
Super User
Super User

Hey,

I guess this will work, at least with my simple test data

check Role = 
IF(CONTAINS('person',person[Alias],USERPRINCIPALNAME())
    ,IF(
        LOOKUPVALUE(person[isSalesPerson],person[Alias],USERPRINCIPALNAME())=1
        ,USERPRINCIPALNAME()
        ,"N/A"
    )
    ,BLANK()
)


If USERPRINCIPALNAME() is not in the column [Alias] the function returns BLANK
If USERPRINCIPALNAME() is a salesperson it returns USERPRINCIPALNAME() otherwise "N/A

Please be aware that LOOKUPVALUE works when only one row matches the condition.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hello,

 

I would like to ask if it is possible to have an example for the code  for different managers levels. Like a nested if look, e.g.

  • if level 01,show this,
  • if level 02, show this but not that part

Is this possible?

Kind regards,

Gabriela 

Anonymous
Not applicable

TomMartens,  Thank you for your reply!

 

I attempted the code you suggested, but received an error that integers are not acceptable.  I replaced the '1' with a 'True' value.

 

I then received an error that I cannot use the CONTAINS() and LOOKUPVALUE() expressionS in a DirectQuery model.

 

Any ideas on how it can be replaced? contains_error.jpg

 

Hey,

 

what is the DATATYPE of the isSalesperson column, in my demotable it is of type int, can you change the datatype in your source table to int?

 

Regards,
Tom

Here are the sql statements I used for testing

create table dbo.person(

	PersonID varchar(50)

,	Alias varchar(50)

,	isSalesPerson int

)



insert into 

--truncate table 

dbo.person

	(PersonID 

,	Alias 

,	isSalesPerson) values

	('tom', 'tmasurfacebook\tmart', 0)

,	('tomtom', 'tmasurfacebook\tmart2', 1)



Update dbo.person

set 

--	isSalesPerson = 0

	Alias = 'invaliddomain\tmart'

where PersonID = 'Tom'


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey,
I just realized that CONTAINS can not be used RLS expressions using DIRECT QUERY mode 😞

 

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Yes, I am trying google for an alternative.  So far the prognosis is not looking good.

Sorry maybe I'm missing something, but the RLS should be a boolean, why you are returning a string?

Are you sure you don't need this?

 

=( Person[Alias] = USERPRINCIPALNAME() ) && ( Person[IsSalesPerson] = 1 )

Anonymous
Not applicable

Marco,  

 

I have a similar issue that I'm hoping you can help me with.  I'm trying to toggle Row Level Security with a Slicer.  We have a users that need to see ONLY their data...which RLS handles perfect.  However, we sometimes will have a user that needs to see data for multiple users.  I created a slicer from a table called "SlicerLoggedIn" and created a column called "View" with two values..."View My Data" and "View All Data". I used the following statement in my RLS:

 

VAR DataView = SELECTEDVALUE('SlicerLoggedIn'[View])
VAR LoggedIn = LookupValue(LoginData[SalesTeam],LoginData[emailAddress],UserPrincipalName())

RETURN
IF(DataView = "View My Data",[SalesTeam]=LoggedIn,1=1)

 

This code does not throw an error, but it only displays the ELSE clause.  I then created a Measure in another table and inserted the follow code:

 

DebugCode =
VAR DataView = SELECTEDVALUE('SlicerLoggedIn'[View])
VAR LoggedIn = LookupValue(LoginData[SalesTeam],LoginData[emailAddress],UserPrincipalName())
VAR DefaultView = "DEFAULT"
RETURN IF(DataView = "View My Data",LoggedIn,DefaultView)
 
I then Displayed this measure on a CARD and as I toggle the slicer, it works perfectly...but the RLS just sits at default.
 
Any ideas?
 
(and yes...I watched your you tube on Debugging DAX.  Good stuff!)
 

Hey @marcorusso,

 

you are 100% correct, seems I got confused.

 

@Anonymous please excuse my confusion.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.