How to filter the current user with measures in your Dynamics 365 Business Central embedded Power BI report.
Embedding Power BI reports directly in Dynamics 365 Business
Central is one of the many synergies of the Microsoft 365
platform. Giving users context relevant analysis right on the page where they need to see it helps make faster and better decisions.
Previously I have looked at how to easily publish a new data set as a web service from BC and use this data in a Power BI
report shown on the BC role center page.
An interesting requirement that came up this week was the need to filter a BI report to only show
data related to the current user.
Initially row
level security was considered; but this was not as easy to implement as using a basic visual filter and the
'Measure of a Measure' approach. Credit goes to the Power BI community, the original solution was described here and here.
This approach relies on your data model having the user emails on the rows you wish to filter to. Add
2 new measures to your data model to the table that shows the user emails.
Adding a measure:
Measure 1:
CurrentUser = USERPRINCIPALNAME()
Measure 2:
FilterByViewer = IF(SELECTEDVALUE(UserApprovalSetup[E_Mail])=[CurrentUser],1,0)
In this example the current table was 'UserApprovalSetup' and the column with user email in was 'E_Mail'.
The second measure now returns a 1 on any row where the 'E_mail' in the underlying table matches the current user. So we can do a simple visual filter:
 |
| Visual filter on the 2nd measure 'FilterByViewer'. Lock or hide if necessary. |
That's it! very simple to use.