none
Conditional RLS - show everything when condition leads to blank or NULL RRS feed

  • Question

  • Hi,

    I have a RLS implemented based on line of business in my org. I have User table and Data table with LOB setup and joined properly. 

    = Deals[Line Of Business] = LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business]). With this it works fine perfectly, when a user belonging to one or multiple LOB's data is shown accordingly.

    Now question is - Managers and above category will not have LOB's defined, they would like to see every LOB values, when they login to Power BI. I tried modifying DAX to below:

    = Deals[Line Of Business] = IF(ISBLANK(LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business]),

    ALL(Deals[Line Of Business]),

    LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business]).

    But when Manager logs in, it doesn't show anything. Since LOB returned is NULL for Manager.

    Can some one guide me the right DAX to be used in my case? When ever LOOKUP returns NULL, I want to show all the data.

    Thanks

    Manoj

    Tuesday, August 6, 2019 7:23 PM

Answers

  • Can you give some context when we say "The relationship setup will over ride"? as per our formula, when ever manager logs in, a blank would be returned as LOB would be NULL in the table. 

    Sorry, that was a flaw in my testing approach.

    But the relationships do appear to be redundant if you are applying security on the Deals table using LOOKUPVALUE against the UserInfo table. You would only need the relationships if you were applying security restrictions directly on the UserInfo table, but if you do that you can't get your manager requirement to work.

    The issue I found was that my test UserInfo table actually had empty strings, not nulls for the manager LOB, so I had to change it as follows to check if _lookupLOB = "" to get it to work. I don't know if a similar issue is affecting you as you can't really visually see a difference between blanks and empty strings. (You could even replace the blanks with some known string like "-ALL-" to make it clearer what the behaviour is for the managers)

    VAR _activeUser = USERNAME()
    VAR _lookupLOB = LOOKUPVALUE(UserInfo[LOB], UserInfo[User], _activeUser)
    RETURN [LOB] = IF( _lookupLOB = "", Sales[LOB] ,_lookupLOB )

    PS. you don't really need the _activeUser as a variable, I just used that so that I could put in hard coded values for testing.

    I tested with a small mockup using the same relationship layout that you have and the above role filter works even if I delete the relationships between UserInfo - DistinctLOB - Deals. So I think this is only adding a performance overhead due to the bi-directional filters.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by manalla Wednesday, August 14, 2019 2:51 PM
    Tuesday, August 13, 2019 10:41 PM
    Moderator

All replies

  • I did try with below too, but doesn't work as expected.

    = Deals[Line Of Business] = IF(ISBLANK(LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business]),

    VALUES(Deals[Line Of Business]),

    LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business]).

    Thanks

    Manoj

    Tuesday, August 6, 2019 7:39 PM
  • Lookup value has an optional last value to return if no match is found and RLS expects an expression that returns true or false, so if you just return the column itself as the alternate value it should work

    Deals[Line Of Business] = LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business] , Deals[Line Of Business] )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, August 6, 2019 8:38 PM
    Moderator
  • Hi Darren,

    I am getting syntax error when I add the alternate value field. Can you suggest me if my tool version supports - SQL Server 2016 and SSDT 2015 is my environment.

    Thanks

    Manoj

    Wednesday, August 7, 2019 8:22 PM
  • Lookupvalue has been around since SSAS 2012 (see https://dax.guide/lookupvalue/) but I can't recall if the alternate result was always there or not. It depends on what the error message you are getting is - can you post the actual error message?

    Another would be to combine the approach of returning the Deals[Line Of Business] column for managers with doing the IF( ISBLANK(...) check (I've used a variable below to avoid repeated lookup calls)

    = VAR _lookup = LOOKUPVALUE(UserInfo[LOB],UserInfo[UserName],USERNAME(),UserInfo[LOB],Deals[Line Of Business])

    RETURN Deals[Line Of Business] = IF(ISBLANK(_lookup), Deals[Line Of Business], _lookup )


    http://darren.gosbell.com - please mark correct answers

    Wednesday, August 7, 2019 9:06 PM
    Moderator
  • Hi Darren,

    Actual warning message, when I add default value for Lookup - "The number of arguments is invalid. Function LOOKUPVALUE must have a value for each specified column reference."

    Your new formula suggestion is in similar lines with what I have tried earlier. Still yours is optimized with VAR, so I used this. But I don't see any records being populated on visual when a user who doesn't belong to any LOB logs in. i.e LOOKUP returning NULL.

    To give little more background - I created a bridge table with distinct LOB's to join tables.

    UserInfo and Deals tables - UserInfo and Bridge table are bi-directional with many to one join and apply filter direction when using RLS. And Bridge table and Deals table are 1 to many join with single direction.

    I hope this is a valid join scenario for this security implementation.

    So, for me still the LOOKUPVAKUE with ISBLANK is not helping. Power BI report visual created from DEALS table, doesn't show any records when user who doesn't belong to any LOB logs in.

    Please help!

    Thanks

    Manoj

    Monday, August 12, 2019 1:57 PM
  • To give little more background - I created a bridge table with distinct LOB's to join tables.

    UserInfo and Deals tables - UserInfo and Bridge table are bi-directional with many to one join and apply filter direction when using RLS. And Bridge table and Deals table are 1 to many join with single direction.

    I hope this is a valid join scenario for this security implementation.

    No, it's not. That relationship setup will override what you are trying to do with the manager logic.

    If you keep that structure there are 2 possible solutions.

    1. Create a separate role for managers with no filtering and just add your managers to that role

    2. Insert a record for every LOB into the UserData table for managers rather than inserting a single record with a blank


    http://darren.gosbell.com - please mark correct answers

    Tuesday, August 13, 2019 1:07 AM
    Moderator
  • Hi Darren,

    Source data is not in my control. I just get data from another SOR and I take care of SSAS and Power BI. Is there any other recommendation? Since LOB can repeat in both the tables, I had to create the bridge table. And with this creation I could best join is what I explained in my previous thread.

    Can you give some context when we say "The relationship setup will over ride"? as per our formula, when ever manager logs in, a blank would be returned as LOB would be NULL in the table. So IF condition should pass all the Deals[Line Of Business] and kind of apply no filter (since all LOB's are passed). I tested the expression and it returns Blank. So am not sure, why this formula is not working. 

    Am I missing anything to correct my relationships? 

    Thanks for your continuous support.

    Thanks

    Manoj

    Tuesday, August 13, 2019 7:02 PM
  • Attaching the image on my relation to be more precise.


    Tuesday, August 13, 2019 8:08 PM
  • Can you give some context when we say "The relationship setup will over ride"? as per our formula, when ever manager logs in, a blank would be returned as LOB would be NULL in the table. 

    Sorry, that was a flaw in my testing approach.

    But the relationships do appear to be redundant if you are applying security on the Deals table using LOOKUPVALUE against the UserInfo table. You would only need the relationships if you were applying security restrictions directly on the UserInfo table, but if you do that you can't get your manager requirement to work.

    The issue I found was that my test UserInfo table actually had empty strings, not nulls for the manager LOB, so I had to change it as follows to check if _lookupLOB = "" to get it to work. I don't know if a similar issue is affecting you as you can't really visually see a difference between blanks and empty strings. (You could even replace the blanks with some known string like "-ALL-" to make it clearer what the behaviour is for the managers)

    VAR _activeUser = USERNAME()
    VAR _lookupLOB = LOOKUPVALUE(UserInfo[LOB], UserInfo[User], _activeUser)
    RETURN [LOB] = IF( _lookupLOB = "", Sales[LOB] ,_lookupLOB )

    PS. you don't really need the _activeUser as a variable, I just used that so that I could put in hard coded values for testing.

    I tested with a small mockup using the same relationship layout that you have and the above role filter works even if I delete the relationships between UserInfo - DistinctLOB - Deals. So I think this is only adding a performance overhead due to the bi-directional filters.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by manalla Wednesday, August 14, 2019 2:51 PM
    Tuesday, August 13, 2019 10:41 PM
    Moderator
  • This is Excellent Darren!

    It worked. I don't know why it dint strike to me to use _lookupLOB = "" instead of ISBLANK. You got it worked, Thanks a lot for continuously replying to my questions. 

    Thanks again for superb support. 

    Manoj

    Wednesday, August 14, 2019 2:51 PM
  • Hi Darren,

    Sorry to poke back. Actually my testing was wrong, I created a role with out access restriction and added manager to that. And then thought the formula with _lookupLOB = "" was working. My mistake and assumed wrong.

    Both _lookupLOB = "" and ISBLANK(_lookupLOB) are not working for me. 

    For sure I see BLANK is being returned when Manager logs in. But this IF logic returning all the LOB's is not working some how.

    Please assist, though this is marked answered.

    Thanks

    Manoj


    • Edited by manalla Thursday, August 15, 2019 9:20 PM
    Thursday, August 15, 2019 2:11 PM
  • I'm not sure how else to help. This is working fine on my test model.

    Have you tried putting in a fixed value something like "-ALL-" instead of leaving a blank? Or try doing a trim. I'm wondering if you have spaces or other non-visible characters in there.


    http://darren.gosbell.com - please mark correct answers

    Friday, August 16, 2019 3:25 AM
    Moderator