Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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()))
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:
Does anyone have a thought on this solution? Thank you in advance!
Solved! Go to Solution.
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
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.
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
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
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.
Is this possible?
Kind regards,
Gabriela
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?
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'
Hey,
I just realized that CONTAINS can not be used RLS expressions using DIRECT QUERY mode 😞
Tom
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 )
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:
Hey @marcorusso,
you are 100% correct, seems I got confused.
@Anonymous please excuse my confusion.
Regards,
Tom
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |