Forum Discussion

DataEngineer314's avatar
DataEngineer314
Regular Visitor
1 year ago
Solved

Display Data Based on User Ownership in Power BI

I need a Power BI table to show:

  • Store Name
  • Rank
  • Total Gross Sales

The Store Name should only appear for stands the user owns. For non-owned stores, the Store Name should be blank, but Rank and Gross Sales should still be visible.

 

Do you have any advice to navigate this, more specifically how to still show data but not store name with RLS?

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi DataEngineer314 ,
    Thanks for dlopesc04 rerply.
    The following is a realization and expansion of his response
    Sample Data

    RLS  
    UserEmail Franchise_ID
    [email protected] 1001
    [email protected] 1002
    [email protected] 1003

     

    Fact_Store    
    Franchise_ID Store_ID Store_Name
    1001 101 One
    1002 102 Two
    1003 103 Three
    1004 104 Four

     

    Fact_Sales    
    Store_ID Date Gross Sales
    101 11/1/2024 100
    102 11/1/2024 400
    103 11/2/2024 300
    104 11/3/2024 200

     

    Create a new table

    User_Store = 
SELECTCOLUMNS(
    Fact_Store,
    Fact_Store[Franchise_ID],
    Fact_Store[Store_ID],
    RELATED(Fact_Sales[Gross Sales])
)

    Create measures

    Store Name Display = 
VAR _ID = 
CALCULATE(
    MAX(Fact_Store[Franchise_ID]),
    FILTER(
        Fact_Store,
        RELATED(RLS[UserEmail]) = USERNAME()
    )
)
RETURN
IF(
    COUNTROWS(
        FILTER(
            User_Store,
            User_Store[Fact_Store_Franchise_ID] = _ID && 
            User_Store[Fact_Store_Store_ID] = SELECTEDVALUE(Fact_Store[Store_ID])
        )
    ) > 0,
    SELECTEDVALUE(Fact_Store[Store_Name]),
    BLANK()
)
    Total Gross Sales = SUM(User_Store[Fact_Sales_Gross Sales])

    Create a column

    Rank = RANKX(User_Store,User_Store[Fact_Sales_Gross Sales],,)

    Relationship

    Final output

    For RLS table you should use this code

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

2 Replies

  • dlopesc04's avatar
    dlopesc04
    Resolver II
    1 year ago

    Duplicate your fact table and remove the store name column from the second table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DataEngineer314 ,
    Thanks for dlopesc04 rerply.
    The following is a realization and expansion of his response
    Sample Data

    RLS  
    UserEmail Franchise_ID
    [email protected] 1001
    [email protected] 1002
    [email protected] 1003

     

    Fact_Store    
    Franchise_ID Store_ID Store_Name
    1001 101 One
    1002 102 Two
    1003 103 Three
    1004 104 Four

     

    Fact_Sales    
    Store_ID Date Gross Sales
    101 11/1/2024 100
    102 11/1/2024 400
    103 11/2/2024 300
    104 11/3/2024 200

     

    Create a new table

    User_Store = 
SELECTCOLUMNS(
    Fact_Store,
    Fact_Store[Franchise_ID],
    Fact_Store[Store_ID],
    RELATED(Fact_Sales[Gross Sales])
)

    Create measures

    Store Name Display = 
VAR _ID = 
CALCULATE(
    MAX(Fact_Store[Franchise_ID]),
    FILTER(
        Fact_Store,
        RELATED(RLS[UserEmail]) = USERNAME()
    )
)
RETURN
IF(
    COUNTROWS(
        FILTER(
            User_Store,
            User_Store[Fact_Store_Franchise_ID] = _ID && 
            User_Store[Fact_Store_Store_ID] = SELECTEDVALUE(Fact_Store[Store_ID])
        )
    ) > 0,
    SELECTEDVALUE(Fact_Store[Store_Name]),
    BLANK()
)
    Total Gross Sales = SUM(User_Store[Fact_Sales_Gross Sales])

    Create a column

    Rank = RANKX(User_Store,User_Store[Fact_Sales_Gross Sales],,)

    Relationship

    Final output

    For RLS table you should use this code

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly