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.  


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.