Power BI: Displaying Anonymous Competitor Data with Store-Specific Filters on SharePoint

[ellen365]

The situation I'm grappling with is as follows:

I've designed a dashboard for a client that displays data from 150 distinct stores. These stores are owned by 55 individuals, with some of them having ownership over multiple stores.

The dashboard I developed showcases Key Performance Indicators (KPIs) for each store and contrasts them with the 6 most comparable stores based on 2023 revenue. Users can select a specific store using a slicer to view its data alongside anonymized data of comparable stores. The intention is for owners to cycle through their stores using this slicer without accessing competitors' specific data. While they'll see metrics from competing stores if they're part of the comparison group, the information remains anonymous, ensuring they can't identify which stores they're looking at. 

I plan to deploy this report on SharePoint. Each owner accesses the report through their unique SharePoint URL, logging in to view the Power BI report tailored to their owned stores.

The challenge lies in how to best implement this.

• Using Row-Level Security (RLS) doesn't seem fitting. This is because it would filter out all rows unrelated to the owner's stores, leading to visuals about comparable stores being devoid of data, as competitor data would be excluded at the row level.

  I've considered two potential solutions:

  1. Deploying 55 separate reports to the Power BI service, one tailored to each owner. The clear drawback is the considerable effort, especially if adjustments are needed later.
  2. Cloning the data model. Here, RLS would be applied to one, while the other remains RLS-free for comparison visuals. The downside is that it doubles the data model's size, leading to a notable dip in performance.

     Neither solution feels optimal, which prompts me to explore alternative approaches.

[ellen365]

I solved the issue by creating a measure checking userprinciplename(), using it as a filter on the slicer to select stores. Now the slicer in the repport adjust to the specific userID that is logged in on sharepoint.

[josef78]

You can also try another way, try prepare more complex model in Power BI which meets your requirements.

E.g. You can have full detail data in model, and use RLS rules which are applied during interactive queries of users. From this data you can create second calculated data table in model (during calculation RLS is not applied), which will be aggregated and anonymized, without any RLS, and will be used for comparasion. But all of it depends on another requirements, which I don't know.

[lbendlin]

#2 is the way to go.