How to do it...

  1. Open a local DirectQuery Power BI data model and select Edit Queries from report view to open the Query Editor.
  2. Create queries against the users and security views developed in Getting ready.
let Source = AdWorksProd,UserSalesCountry = Source{[Schema = "BI", Item = "vDim_UserSalesCountrySecurity"]}[Data]in UserSalesCountry
  1. Duplicate an existing query via the right-click context menu and revise the Item value to the name of the SQL view.
  2. Create one additional query, which retrieves the unique values of the column to be secured (Countries):
let Source = AdWorksProd,Territory = Source{[Schema = "BI", Item = "vDim_SalesTerritory"]}[Data],Countries = Table.SelectColumns(Territory,{"Sales Territory Country"}),DistinctCountries ...

Get Microsoft Power BI Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.