Assignment – 11: Roles & Security
Source – House Price
Task – Clean & Secure the Report
Initial data transformation using power query editor:
Let’s have a look at the Source tables:
We are going to perform sone basic transformations:
1. Create Colum headers for the House Price data table set by deleting the top 2 rows and using the 3rd as Headers by clicking on “Use First Row as Headers” in the Transform tab.
2. Then will consider 1st row of Property types table data set as column headers by applying the above method.
3. Then in the House Price dataset we will delete the 2 columns with null values.
The Outcome is shown below:
House Price table:
Property types table:
Task 1- Create a basic table showing data for sales of houses in different areas:
We will create a Table visual in the Report view section
Task 2 - Create a role for each house type, filtering using the PropertyType code.
For this task we click on “Manage Roles” in the Modelling tab and we create 5 roles by clicking on the create option and using the initials as substitute value
Similarly, we create the other 4 and we click on Save.
To check if this is actually working out we again click on the Modelling tab in the “View as” option in the Security section.
We can choose any of the roles. I have selected the Detached role, the below screenshot shows only the Detached type Building data in the table visual.
Task 3 - Publish the report in Power BI Service and check the roles
We saved our report and now go the Home section and click on Publish
Now, Log in to Power BI Service with the credentials, Then, we have to select the workspace under which the report needs to be saved.
Now, the pop-up window will appear which shows the following
In power bi service:
Go to my workspace, Under Datasets click on the three dots beside your dataset(i.e. House Price) name and click on Security option
This will take us to the Row level Security window.
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.
we need to enter email address and click on Add then click on Save.
Now, you can test the role by clicking on three dots beside each role and selecting Test as role option.
Here, lets click on Detached(1) role and click on Test as role option. Then you can see the role information.
Similarly, you can test the other four roles after clicking on Back to Row-Level Security.
Assignment-12: Bookmarks & Drill Through
Source – Zoo
Task – Working with Bookmark as action
Initial data transformation using power query editor:
Here we made 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.
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
I have renamed the Page 1 in the Report view as Creatures.
Then, I have inserted images of Fire, Grass & Water.
Task 2: Create a page with a table for each
Click on + icon in the Report view 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 Fire in the list of checkboxes.
· Set the table title to Fire monsters under Format section and align the title to the centre.
Now, we have animals of Fire type page created successfully as shown below:
Now, Click on + icon in the Report view page and name this new page as Grass.
Similarly repeating the above steps, we have animals of grass type page created successfully as shown below:
Again, Click on + icon in the Report view page and name this new page as Water.
Now, we have animals of water type page created successfully as shown below:
Task 3: Creating Bookmarks
For Fire page:
· Click on Fire page and click on View section on the top.
· Click on Bookmarks and click on Add under this section.
· Rename the Bookmark 1 to Fire Monsters.
· Click on the ... dots beside Water monsters and click on Update.
We will follow the same procedure for the Grass & Water pages.
Now, we can see the Bookmarks we have created, on the Bookmarks pane in the right:
Task 4) Lets hide the pages of Water, Grass and Fire so that the only way user can reach the page is by clicking the relevant image on the first page i.e. Creatures
Right click on the Fire page and select Hide Page option. Now you can see the eye symbol cross marked before the page name which indicates now it's a hidden page.
Repeat the same process for Water and Grass Page.
Now, We 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, follow the same process for Water and Grass Images
Now, let’s 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.
· Set Border and Shadow to On for visual appeal.
· Repeat the same process for Grass and Fire pages.
Now, Our Images in Creatures page are ready with bookmark actions.
· 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
We Saved the changes and gave a name to the file as Zoo.
I have selected the default Workspace in power bi service.
Click on Select to publish.
We can check the bookmark actions in power bi service. Here we don't need to hold Ctrl key, we can directly click on images which navigates to the respective pages and can navigate back to the Creatures page.
Here also we won't see Water, Grass and Fire Pages individually as they are hidden.
Source – Authors
Task – Create Tooltips
Open the Power BI report Authors.pbix file.
Task 1) Create a tooltip page to show the volume of sales for each title:
Let’s first create a new page named Tooltip:
In Tooltip page, Go to Format pane under Visualizations section and
Click on Page Information and select the Tooltip as On.
Click on Page size and select Type as Custom. I have customized the size which is as below:
Lets create a visual in this tooltip page:
Click on Clustered bar chart. 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. Lets rename the title as Books for this author.
By Default, the Tooltip to On and Type as Report Page.
Let’s drag Author and drop it to Add tooltip fields under Tooltip section of Visualizations pane.
Now, our Books for this author tooltip page is 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 2) create and display a measure to show the name of the author in your tooltip:
Go to 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:
Now, Save this report as Authors info and Publish in Power BI Service
In the Power BI Service Workspace, when we place the cursor on any Author in the Table visual then the following will appear as the tool tip for it.