Display Data Based on User Ownership in Power BI

[DataEngineer314]

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?

[Anonymous · Accepted solution]

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

RLS
UserEmail	Franchise_ID
A@email.com	1001
B@email.com	1002
C@email.com	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

[dlopesc04]

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