100 shops. 100 reports or 1 report ?

[frano72]

Lets say you have 100 shops across a country and you have a report that is the same and you want each store to be able to run the report and view the report on data from their shop.  (i'm not bothered about security aspects of data for this question).

ideally you'd want a person to click on the report and then based on some information about them as a power bi user, dynamically filter the underlying data to the shop they are at.  another version would be to set a default for the person so they may need to select their home shop the first time, but the report would remember for subsequent runs.

i'm not so keen on 100 x pbix files that you have to keep in synch or that the person has to select their store out of a slicer list every time they go into the report.

any ideas ?

[AllisonKennedy · Accepted solution]

Row Level Security can be used in combo with the DAX USERNAME() function to get info about the logged in user and automatically filter the report to show them their data only. 

https://docs.microsoft.com/en-us/dax/username-function-dax

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

Alternatively, in the PowerBI.com service, it now remembers your previous selections so you don't have to re filter every time if you deviate from the report design.

https://powerbi.microsoft.com/en-us/blog/announcing-persistent-filters-in-the-service/

Final option is to show the users how to use Personal Bookmarks.

https://powerbi.microsoft.com/en-us/blog/announcing-personal-bookmarks-in-the-power-bi-service/

[amitchandak · Accepted solution]

@frano72 , Try to Use RLS

refer these links

Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

[frano72]

Thanks Amitchandak

[AllisonKennedy · Accepted solution]

Row Level Security can be used in combo with the DAX USERNAME() function to get info about the logged in user and automatically filter the report to show them their data only. 

https://docs.microsoft.com/en-us/dax/username-function-dax

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

Alternatively, in the PowerBI.com service, it now remembers your previous selections so you don't have to re filter every time if you deviate from the report design.

https://powerbi.microsoft.com/en-us/blog/announcing-persistent-filters-in-the-service/

Final option is to show the users how to use Personal Bookmarks.

https://powerbi.microsoft.com/en-us/blog/announcing-personal-bookmarks-in-the-power-bi-service/

[frano72]

Thanks Allison !