Tag Archives: Power BI Query Editor

Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters

Power BI Desktop and SQL Server Dynamic Data Masking

As I promised in my earlier post, in this article I show you how to leverage your Power BI Desktop model using Query Parameters on top of SQL Server 2016 Dynamic Data Masking (DDM). I also explain very briefly how to enable DDM on DimCustomer table from AdventureWorksDW2016CTP3 database. We will then create a Power BI Desktop model with Query Parameters on top of DimCustomer table. You will also learn how to create a Power BI Template so that you can use it in the future for deployment.

Note: If you want to learn about using a List output in Power BI Desktop Query Parameters have a look at the next post of these series “Power BI Desktop Query Parameters, Part 3, List Output“.

Use Cases

In the previous post I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. In this post you learn :

  1. Using Query Parameters on top of SQL Server Dynamic Data Masking (DDM)
  2. Query Parameters in Power BI Template


Just like the Part1 of Power BI Query Parameters, you require to meet the following requirements to be able to follow this post:

  1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
  2. SQL Server 2016 (You can download SQL Server 2016 Developer Edition for free)
  3. Adventure Works 2016 CTP3


I’m not going to provide much details about DDM as you can find lots of information here. But, to make you a bit familiar with Dynamic Data Masking I explain it very briefly.

Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a new feature available in SQL Server 2016 and also Azure SQL Database. DDM is basically a way to prevent sensitive data to be exposed to non-privileged users. It is a data protection feature which hides sensitive data in the result set of a query. You can easily enable DDM on an existing table or enable it on a new table you’re creating. Suppose you have two groups of users in your retail database. Sales Persons and Sales Managers. You have a table of customers which in this post it is DimCustomer from AdventureWorksDW2016CTP3. This table contains sensitive data like customers’ email addresses, phone numbers and their residential adders. Based on your company policy, the members of Sales Persons group should NOT be able to see sensitive data, but, they should be able to all other data. On the other hand the members of Sales Managers group can see all customers’ data. To prevent Sales Persons to see sensitive data you can enable Dynamic Data Masking on the sensitive columns on DimCustomer table. In that case when a sales person queries the table he/she will see masked data. For instance he see uXXX@XXX.com rather than user@domain.com.

Create a table with DDM on some columns

It’s easy, just put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it should look like this:

CREATE TABLE Table_Name   (ID int IDENTITY PRIMARY KEY,    Masked_Column1 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),    Masked_Column2 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),




Alter an existing table and enable DDM on desired columns

As you guessed you have to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL should look like:






For more information please refer to MSDN.

Power BI Template

A template is basically a Power BI file that represents an instance of a predefined Power BI Desktop which includes all definitions of the Data Model, Reports, Queries and parameters, but, not includes any data. Creating Power BI Templates is a great way to ease the deployment of existing models. Creating templates is very easy, you just click File –> Export –> Power BI Template. We will look at this more in details through this article.


You are asked to implement a new level of security on customers’ data (DimCustomer on AdventureWorksDW2016CTP3 database) so that just privileged users can see the customers’ email, phone numbers and residential address. Privileged users are all members of “SalesManager” database role. You are also asked to prevent “SalesPerson” database role to see sensitive data. But, all members of both “SalesManager” and “SalesPerson” database roles can query DimCustomer table. The users should NOT have SQL Server logins.

Continue reading Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters

Power BI Desktop Query Parameters, Part 1

Power BI Query Parameters

One of the coolest features added to the April 2016 release of Power BI Desktop is “Query Parameters”. With Query Parameters we can now create parameters in Power BI Desktop and use them in various cases. For instance, we can now define a query referencing a parameter to retrieve different datasets. Or we can reference parameters via Filter Rows. Generally speaking we can reference parameters via:

  • Data Source
  • Filter Rows
  • Keep Rows
  • Remove Rows
  • Replace Rows

In addition, parameters can be loaded to the Data Model so that we can reference them from measures, calculated columns, calculated tables and report elements.

In “Power BI Desktop Query Parameters” series of articles I show you how to use Query Parameters in different scenarios.


In this article I’ll show you some use cases of Query Parameters based on some scenarios as below:

  1. Parameterising a Data Source
  2. Using Query Parameters in Filter Rows

You’ll learn more about Query Parameters in the next articles “Power BI Desktop Query Parameters, Part 2, SQL Server Dynamic Data Masking Use Case” and “Power BI Query Parameters, Part 3, List Output


You’ll require to meet the following requirements to be able to follow this post:

  1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)

Note: As Dynamic Data Masking (DDM) is a new feature of SQL Server 2016 and it is not available in the previous versions of SQL Server you need to install the latest version of SQL Server 2016. So you will need SQL Server 2016 and Adventure Works CTP3 only if you want to use Query Parameters on top of Dynamic Data Masking (DDM).

Scenario 1: Parameterising a Data Source

Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns. To make it more clear I break down the scenario to some more specific use cases.

Use Case 1: Parameterising Data Source to Connect to Different Servers and Different Databases

Suppose you have different customers using the same database schema. But, the databases hosted in different instances of SQL Server and also the database names are different. With Query Parameters we can easily switch between different data sources then publish the reports to each customers’ Power BI Service.

  • Open Power BI Desktop
  • Click Get Data
  • Select “Blank Query” from “Other” then click “Connect”Power BI Desktop Create Blank Query
  • In Query Editor window click “Manage Parameters” from the ribbon

Continue reading Power BI Desktop Query Parameters, Part 1

How to Define A Measure Table in Power BI Desktop

In this post I show you a simple trick to make your Power BI model more organised and more readable. I call it creating a “Measure Table”. Let me explain. The story is that I was working on a model with lots of tables. The database schema was NOT a proper star schema so there were a bunch of measures spread into lots of different tables. On top of that we’ve created lots of calculated measures with different home tables which made it really hard to find a particular measure or calculated measure. I thought, well, when it is that hard to find the calculated measures at development time how hard it could be for a customer to find, understand and use the measures we created. The visibility of the calculated measures could be an issue when we have lots of measures in lots of different tables. You will soon feel the issue in customer training sessions when you need to navigate between lots of different tables to find a calculated measure.

Consider you create a Power BI model with direct connect to a SSAS Multidimensional instance. You will immediately notice that all measure groups have a special calculation icon (Measure Group Icon in Power BI) rather than a normal table icon (Table icon in Power BI) which makes the measure groups more recognisable for the end users. For instance, you can easily find any calculated measure related to “Internet Sales” under the “Internet Sales” measure group.

Measure Groups in SSAS Multidimensional Dirct Connect

I know, we can search and find the measures very easily, but, our model would be more organised and more user friendly if we can put all measures in one or more tables which contain just related calculated measures and nothing else. For instance, we can create a measure table for time intelligence calculations and name it “Sales Time Intelligence Measures” and put all  calculated measures like “Sales YTD”, “Sales LYTD”, “Sales Period Over Period” on it. It will make your model nice and clean, easy to use and easy to learn for your customers. It will also help you to train your customers more easily.

In this article I’ll connect to a SQL Server instance and will use the famous Adventure Works database. I also show you how to get the job done in both “Import” and “DirectQuery” modes as there are some limitations applied to the DirectQuery mode which makes it harder to do what we want.

Lets start.

Continue reading How to Define A Measure Table in Power BI Desktop