Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  DAX Formula to restrict data

    Posted 22 days ago

    Please can someone help with a row level security query. 

    I have the following tables

    Entity A

    User

    PaulSmith@A.com

    JohnSmith@A.com

    CarlSmith@A.com

     

    Entity B

    Name

    Icon

    A

    Z

    B

    Z

    C

    X

    D

    X

    F

    C

    ZA

    C

     

    I need to create a DAX formula to allow row level security on Entity B for the following logic

    If a user is not in Entity A table then the user should be restricted to see only records where Icon <> C.  If the user is in Entity A then they will have access to all records in Entity B

    Any help much appreciated



    ------------------------------
    Ben Hopes
    FY3 9HR
    ------------------------------


  • 2.  RE: DAX Formula to restrict data

    Posted 18 days ago
    Edited by Kaz Shakir 18 days ago
      |   view attached
    @Ben Hopes,
    I think this is relatively straight forward.  I would create three measures:

    1. First, a measure to determine the current user:
    CurrentUser = USERNAME()​
    ​Note: there is also another function called USERPRINCIPALNAME() - you should try both to see which one gives you the result you need.

    2. Next, I would create a measure to determine if the current user is in Entity A:
    CurrentUserInEntityA = 
    VAR _entityAMatchedRows =
        FILTER(
            ALL('Entity A'),
            'Entity A'[User] = [CurrentUser]
        )
    RETURN
        COUNTROWS(_entityAMatchedRows) > 0​

    3. And third, I would create a measure to determine whether to show a given row in the current filter context:

    DisplayRow = 
    VAR _currentIcon = 
        MIN('Entity B'[Icon])
    RETURN
        IF(
            AND(
                NOT([CurrentUserInEntityA]),
                _currentIcon <> "C"
            ),
            "Display",
            "Don't Display"
        )

    And then you can create a simple table visual to show Entity B, and use the [DisplayRow] measure as a filter on that visual:




    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    HopesExample_v1.pbix   28 KB 1 version