Forum Discussion
Power BI Embedded and multitenant support
What I would recommend would be to do another import of your data but summarize the data in Power Query such that all of the details are removed and then base your measure calculation (if needed) on that table. So, for example, you could do a "Group By" in Power Query to remove row level detail. Then, give everyone access to that table in RLS, or portions of the table that are relevant. For example, if you summarize by industry and only want them to see the measure calculated by their particular industry.
What I would recommend would be to do another import of your data but summarize the data in Power Query such that all of the details are removed and then base your measure calculation (if needed) on that table. So, for example, you could do a "Group By" in Power Query to remove row level detail. Then, give everyone access to that table in RLS, or portions of the table that are relevant. For example, if you summarize by industry and only want them to see the measure calculated by their particular industry.
Hi guys,
A short update from my side. Based on the solution proposal from @smoupre I think I have a good enough implementation.
I ended up creating an additional sub model of pre-aggregated data. The Power BI “GROUP BY” functionality in Query Editor helped a lot.
The solution for me was:
- Use RLS to limit the access only to the user company data
- Determine measures/values that a user would need to see aggregated for all other companies (tenants)
- Determine dimensions (slicer values or categories in the visual) we need to see for all companies
- Use “Group By “ function to create additional data sources containing aggregations of measures across dimensions calculated below.
- At the end you get a sub-model of tables that are not collected to your main power bi model
- Enjoy playing with a safe way to compare your company data against aggregated others J
Note: Depending on the amount of data the “Group by” functionality tends to be extremely slow. I ended up creating views/ queries in the database which delivers aggregated data so that I don’t need to use Group By. Group BY in power BI is useful to test the approach or if you don’t have access to the database or if your data source is not a database.
@smoupre, thanks for the idea.
Thanks for your update Ermin. I will try the same approach, but the hard thing for us is that almost every visual has a comparison version. Instead of aggregating, I might have to copy the whole model twice and add RLS to one, while the copy will be used for comparison.
Anonymous,
this may be even an easier approach than the one I 'have chosen, since you just need to duplicate your data sources. However, I didn’t go that way because it seemed to be such a waste of storage to me. :manhappy:
Additionally, I would expect to gain on performance when dealing with the pre-aggregated values.
However, approach you selected is easier to implement and more flexible in case you need to add new comparison measure or dimension later.
It would be interesting to get your feedback after you gain some expirience with your approach. (Issues you had, workarounds, do and don’ts, pitfalls to be aware of, etc…)