Power BI Service

##powerbi_service ##assignments_11_to_13 ##ineuron

Manirathnam Babu Kuruma Nov 27 2020 · 14 min read
Share this

Assignment-11: Roles & Security

Objective – Power BI Desktop, Cloud Service and End to End Workflow

Use Case – House Price

Source – House Price

Analytics – Clean & Secure the Report

Report/Dashboard – Nariman Insights

Before implementing the tasks, lets talk about what is the concept of Row-level security , importance of Roles and  why we need to manage it.

Row-level security (RLS) with Power BI :

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn't restrict this data access.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. For Analysis Services or Azure Analysis Services lives connections, you configure Row-level security in the model, not in Power BI Desktop. The security option will not show up for live connection datasets.

Roles:

We can define roles and their rules within Power BI Desktop. When you publish to Power BI Service, it also publishes the role definitions.

Importance of Roles:

Any organization requires the need of different roles to manage or monitor different operations based on type of region, operation etc. "Manage Roles" option in power bi helps to restrict  access to only required information to specific users excluding other information.

Why we need to manage roles:

Security comes into picture when we talk about different roles and their access to information. No organization would want to reveal entire information to all the roles. So, we need to define and manage roles and their access rules.

To define security roles within Power BI Desktop, we follow these steps.

Step 1) Import data into your Power BI Desktop report, or configure a DirectQuery connection.

Step 2) From the Modeling tab, select Manage Roles.

Step 3) From the Manage roles window, select Create.

Step 4) Under Roles, provide a name for the role.

Step 5) Under Tables, select the table to which you want to apply a DAX rule.

Step 6)  In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or false. For example: [Entity ID] = “Value”.  

Step 7) After you've created the DAX expression, select the checkmark above the expression box to validate the expression.

Step 8) Select Save.

Note: we can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured. 

Validate the roles within Power BI Desktop:

After you've created your roles, test the results of the roles within Power BI Desktop.

Step 1) From the Modeling tab, select View as Roles.

Then the View as roles window appears, where you see the roles you've created.

Step 2)  Select a role you created, and then select OK to apply that role.

The report filters data based on what that user can see.

Note: You can also select Other user and supply a given role using User Principal Name (UPN) as that's what the Power BI service and Power BI Report Server use. Note that the Other user displays different results only if you're using dynamic security based on your DAX expressions.

Manage roles within Power Bi Service level:

Once we publish the report to power bi service. we can manage and test our roles in power bi service level also. we can create new roles or we can manage existing roles.

we follow these steps:

Step 1) In the Power BI service, select the More options menu for a dataset.

Step 2) Select Security.

Security will take you to the RLS page where you add members to a role you created.

Add members:

Add a member to the role by typing in the email address or name of the user or security group.

You can also see how many members are part of the role by the number in parentheses next to the role name, or next to Members.

Remove members:

You can remove members by selecting the X next to their name.

Validating the role within the Power BI service:

You can validate that the role you defined is working correctly by testing the role with these steps:

Step 1) Select More options (...) next to the role.

Step 2) Select Test as role.

Since we got an overview, lets implement all the above steps with the given dataset "House price".

Initial data transformation using power query editor:

House Price table:

Lets remove the top first two rows  using  Remove Rows  under Home section. 

A window pop ups asking no. of top rows to be deleted. we need to specify value 2 in this case.

Lets make the third row as column header by clicking on Transform section and selecting  Use First Row as Headers option.

Removed null columns by right clicking on those columns and clicking on Remove Columns option.

Removed null rows which are in between using Remove Blank Rows option under Remove Rows of Home section.

Property types table:

Here, we just need to make the first column as header using Use First Row as Headers option under Transform section.

Now, click on Close & Apply to apply changes and switch to the desktop mode.

Now, the resultant tables looks fine as shown below:

House Price table:

Property types table:

Task 1) Lets create a table visual showing Handover Date, PostCode, Location and SalesPrice.

Lets go to Report view section, select table visual and drag required columns to field section.

The resultant visual is shown below:

Task 2) Lets create roles for each house type, filtering using the PropertyType code.

To do this,

Click on Manage roles under Modeling section.

Manage roles window pop ups and we need to click on Create option

Name it as "Detached" ,select "House Price" table option then click on the three dots and select add filters and select "Property Type".

Then substitute the Value as "D" and click on Save.

Similarly, we need to create other four roles as shown below:

Task 3) Lets check if each role is working or not.

To do this, click on View as under Modeling section.

View as roles window pop ups showing all the roles that we have created.

Now, select Detached checkbox and click on ok.

Now, you can see the filtered table as a Detached role as shown below:

Now, you can click on Stop viewing and test another role by again clicking on View as and selecting another checkbox.

That's how we can test all the roles within power bi desktop.

Task 4) Lets publish the report and check the roles in power bi service.

To do this,

Save the changes and give a name to the file.

Make sure that you have an empty workspace created for this project in order to keep the report in a specific workspace or you can select default workspace i.e My workspace.

I have created a workspace House Prices in power bi service.

To create a workspace, click on Workspaces in power bi service and click on Create a workspace option and give a name of your choice.

Under Home section, click on Publish.  A window pop ups prompting you to select the workspace. in my case i have selected House Prices.

Click on Select. This gives you a message as successfully published and a link click to view report in power bi service. on clicking it, logs you into your power bi service on your browser.

In power bi service:

Go to House Prices workspace, Under Datasets click on the three dots beside your dataset name (in my case assign11 dataset) and click on Security option

This will take you to Row-Level Security page showing the roles.

Here, we can add members for each role by entering their email addresses. in my case i have given access to myself for all the roles(since, this is not a real time scenario).

we need to enter email address and click on Add then click on Save.

Here, i have added one member for each role.

Now, you can test the role by clicking on three dots beside each role and selecting Test as role option.

Here, lets click on ... dots of Detached(1) role and click on Test as role option. Then you can see the role information.

Here you can see a secure report:

Similarly, you can test  the other four roles after clicking on Back to Row-Level Security .

Assignment-12: Bookmarks & Drill Through

Objective – Power BI Desktop, Cloud Service and End to End Workflow

Use Case – Working with Bookmark as action

Source – Zoo

Analytics – Create Bookmark

Report/Dashboard – Bookmark as action

Before implementing the tasks, lets talk about bookmarks and drill through.

What are bookmarks?

Bookmarks capture the currently configured view of a report page, including filters, slicers, and the state of visuals. When you select a bookmark, Power BI takes you back to that view.

Bookmarks are useful for effective story telling.

What is drill through?

When applied a visual (source visual), navigates to another page showing statistics/analysis of that particular data point using another visual.

Drill through comes in handy when we need to show analysis of a particular data point or product of a specific visual in another page without disturbing other visuals.

We can toggle between the reports using bookmarks and drill through.

Lets implement bookmarks and drill through with the given dataset "Zoo".

There is only one initial data transformation we did here, making the first row as column header using Use First Row as Header option under Home section of power query editor.

Also changed the datatype of National Pokedex number column to whole number.

Click on Close&Apply to apply changes and switch back to desktop mode.

Now the table Nature looks fine as shown below:

Task 1)  Try to make a report which has a page for each kind of Image. When you click the image of that creature you will see all the animals of the same type:

Lets go to report view and rename Page 1 to Creatures.

Now, to insert images of Fire, Grass and Water:

Click on Insert section on the top and click on Image. A window pop ups and you need to select the path of the image or simply browse through it. Click on Open button to load the image into the Creatures page of power bi desktop.

Task 2) Now lets create pages for the animals of Fire, Grass and Water:

Click on + icon beside Creatures page and name this new page as Water. 

=>Create a table visual with columns National Pokedex number, English Name, Evolves into, Japanese name.

=>Now, Select the table visual and go to Filters on this visual under Filter pane.

=>Click on Add data fields here under Filters on this visual section.

=>Drag and drop Nature Symbol column to this Add data fields here and

Select filter type as Water in the list of checkboxes.

Set the table title to Water monsters under Format section and align the title to the center.

Now, we have animals of water type page created successfully as shown below:

Now, click on icon beside Water page and name this new page as Grass. 

Follow the same steps which are previously stated with => symbol.

Select filter type as Grass in the list of checkboxes.

Set the table title to Grass monsters under Format section and align the title to the center.  

Now, we have animals of grass type page created successfully as shown below:

Now, click on icon beside Grass page and name this new page as Fire. 

Follow the same steps which are previously stated with => symbol.

Select filter type as Fire in the list of checkboxes.

Set the table title to Fire monsters under Format section and align the title to the center.  

Now, we have animals of fire type page created successfully as shown below:

Task 3) Lets create bookmarks for each page.

For Water page:

Click on Water page and click on View section on the top.

Click on Bookmarks and click on Add under this section.

Rename the Bookmark 1 to Water monsters.

Click on the ... dots beside Water monsters and click on Update

For Grass page:

Click on Grass page and click on View section on the top.

Click on Add under Bookmarks section.

Rename the Bookmark 2 to Grass monsters.

Click on the ... dots beside Grass monsters and click on Update

For Fire page:

Click on Fire page and click on View section on the top.

Click on Add under Bookmarks section.

Rename the Bookmark 3 to Fire monsters.

Click on the ... dots beside Fire monsters and click on Update

Now, we can see the created bookmarks when we click Bookmarks on the top section:

Task 4) Lets hide the pages of Water, Grass and Fire so that  only Creatures page is visible.

=>Right click on Water page and select Hide Page option. now u can see the eye symbol cross marked before the page name which indicates now it's a hidden page.

Similarly, we can hide Grass and Fire pages:

=>Right click on Grass page and select Hide Page option and

=>Right click on Fire page and select Hide Page option.  

Now, you can see the hidden pages as shown below:

Task 5) Lets bring actions to the images which navigate to respective bookmarked pages.

=>Click on Creatures page and select Fire image

Under Format Image pane, set Action section to On

Under Action section, select the Type as Bookmark

Select the Bookmark as Fire monsters

=>Now, select the Grass image

Under Format Image pane, set Action section to On

Under Action section, select the Type as Bookmark

Select the Bookmark as Grass monsters

=>Now, select the Water image

Under Format Image pane, set Action section to On

Under Action section, select the Type as Bookmark

Select the Bookmark as Water monsters

Now, lets insert Back buttons for Grass, Fire and Water pages:

=>Click on Water page and click on Insert option on the top.

Click on Buttons under Elements section and select Back button 

Set the Button Text to On under Visualizations pane and give the text name as Back. align it to the center of the button and increase the text size for visual appeal.

Set the Action to On and select the Type as Back.

Set Border and Shadow to On for visual appeal.

=>Do the same for Grass and Fire pages.

Now, Our Images in Creatures page are ready with bookmark actions.

To check:

=>Hold Ctrl and click on Fire Image, this navigates to Fire page where the table visual shows all columns belonging to the Nature Symbol of type Fire.

=>Now, hold Ctrl and click on Back button in the Fire page to navigate back to the Creatures page.

=>Similarly, you can check for Grass and Fire images.

Task 6) Publish to power bi service

To do this,

Save the changes and give a name to the file as Zoo

Make sure that you have an empty workspace created for this project in order to keep the report in a specific workspace or you can select default workspace i.e My workspace.

I have created a workspace Zoo in power bi service.

To create a workspace, click on Workspaces in power bi service and click on Create a workspace option and give a name of your choice.

Under Home section, click on Publish.  A window pop ups prompting you to select the workspace. in my case i have selected Zoo. Click on Select to publish.

Now, you can check the bookmark actions in power bi service. Here you don't need to hold Ctrl. you can directly click on images which navigates to the respective pages and can navigate back to the Creatures page.

Here you won't see Water, Grass and Fire Pages individually as they are hidden.

Creatures Dashboard
Fire monsters Report

Assignment-13: Tooltips

Objective – Power BI Desktop, Cloud Service and End to End Workflow

Use Case – Working with Tooltips

Source – Authors

Analytics – Create Tooltips

Report/Dashboard – Bookmark as action

Before implementing the task, lets talk about tooltips

What are tooltips?

Tooltips are an elegant way of providing more contextual information and detail to data points on a visual. For example, The following image shows a tooltip applied to a chart in Power BI Desktop.

When you hover your mouse over a visual segment, you can see the default tooltip.

How to customize tooltips?

To create a customized tooltip, in the Fields well of the Visualizations pane, drag a field into the Tooltips bucket, shown in the following image. In the following image, three fields have been placed into the Tooltips bucket.

We can also create tooltip pages which we will see here in the tasks.

Here we have given Authors pbix file with the page List of books.

Task 1) Check the author name when second column is sorted in descending order

if we sort the second column Count of Title in descending order , we can see the author Rowling, J.K. as the first name in the list.

Task 2) Create a tooltip page to show the volume of sales for each title:

Lets create Books for this author tooltip page:

Click + icon for a new page and name it as Books_tooltip

Your tooltip can be any size, but keep in mind that tooltips hover over the report canvas, so you might want to keep them reasonably small. 

So, in Books_tooltip page, Go to Format pane under Visualizations section and

Click on Page size and select Type as Custom

By default, Power BI Desktop fits your report canvas to the available space on the page. Often that's good, but not in the case of tooltips. To get a better sense and view of what your tooltip will look like when you're done, you can change the Page View to custom size.

To do that, select the View tab from the ribbon. From there, select Page View >Custom Size

My tooltip page custom size:

Lets create a visual in this tooltip page:

Click on Clustered bar chart (or any visual of your choice). Then the selected visual automatically fits into this tooltip page. we can adjust the visual size to cover the entire tooltip page.

Drag Title to axis field and Volume to Values field. here we are plotting bar chart "Volume by Title". Lets rename the title as Books for this author as it looks meaningful to see this tooltip report whenever we hover on Author category.

Keep the Tooltip to On  and select Type as Report Page

Drag Author and drop it to Add tooltip fields here under Tooltip section of Visualizations pane.

Now, our Books for this author tooltip page is ready as shown below:

Keep the tooltip on for List of books page and select Type as Report Page under Tooltip section of Visualizations pane.

Now, when you hover your mouse on each author, Books for this author tooltip appears automatically like shown below:

Task 3) Lets create dynamic title on the tooltip instead of a static one.

Go to Books_tooltip page, select the Books for this author visual and go to Title option under Format section of Visualizations pane.

Remove the existing title and click on fx. (here fx indicates conditional formatting on title)

Then a window pop ups like shown below:

Note: Here, we are formatting field value of title based on selected field  i.e Author in this case from the list.

From the dropdown list of Based on field just select Author 

  and click on ok.

Now, we have successfully created dynamic title for our tooltip.

The tooltip with dynamic title looks like below:

Task 4) Publish to power bi service

Save the changes, click on publish and select  a workspace, here i select Authors Info workspace which i have created before this task.

Click on Select. Then it will be published to power bi service.

Reference: https://docs.microsoft.com/en-us/power-bi/

Thanks to @Amit Bose for the wonderful sessions and guidance.

Thanks to @ineuron for this amazing blogging platform.

Comments
Read next