PC Encryption Dashboard – Part 3

After getting required information.

4. Presenting Information

At this step, we’ll show the extracted information on a report so that viewers can get their own knowledge from those information easily. First of all, you need to have an idea about how the report interface should be. You can draft it on a paper like this:


Now let’s make it real. Go to Report View. In View tab, select Show Gridlines and Snap Objects to Grid to make your design work easier.

4.1 12-Aug Total Unencrypted Chart

Select Clustered Bar Chart from Visualizations panel. Information to show up are from the 12Aug table. Select 12Aug from the FIELDS panel. Select CountryCode as Axis, Count for Asset ID as Value.

17_12AugTotalChart 1

Then select the Format tab in the Visualizations panel and make this chart look better:

  • Turn on Data labels
  • Select Data colors – I like Pink for this chart
  • Edit Title Text, Font color, Background Color, Center Alignment, Text size to 10.

4.2 Fixed PCs Chart

Select Clustered Column Chart from Visualizations panel. Information to show up are from the FixedPCs table. Select FixedPCs from the FIELDS panel. Select CountryCode as Axis, Count for Asset ID as Value. Put a Table virtualization below the FixedPCs chart to show the PC details, select the columns Service Tag, slbitbuilding and CountryCode in the FixedPCs table in FIELDS panel. Tweak the format a bit. The result should look like this:

18_FixedPCChart 1

4.3 Not Fixed PCs Chart

Steps are similar to the Fixed PCs chart. Result should like this:

19_NotFixedPCChart 1

4.4 New PCs Chart

Steps are similar to the Fixed PCs chart. Result should like this:

20_NewPCChart 1

4.5 Report Title

Just add a Text box, enter the report title, other contents and rearrange all the charts, we should be happy with the final design:


Congratulations! But the most interesting part of a colorful report hasn’t come yet. Let’s go to the next step.

4.6 Making DrillThrough

A useful function that we need from this dashboard is when we click a country in the 12-Aug Total Unencrypted, all other charts will be filtered automatically to show only data of that country. This automatic filter function is called Drillthrough. Until step 4.5, drillthrough doesn’t work in this dashboard. We’ll enable it with below steps.

Select 12-Aug Total Unencrypted chart. In Visual tools Ribbon, select Edit interactions.

22_Edit Interactions

The interaction icons will show up in each chart:

22_Edit Interactions_2

To allow a filter in 12-Aug Total Unencrypted chart affect other charts, we’ll select the filter icon in other chart – turn it to black. But now we can see the drillthrough filter still hasn’t taken effect. That’s because currently there’s no any relationship between the 12Aug table and the FixedPCs, NotFixedPCs or NewPCs tables. Hence, the interaction or drillthrough filter can’t affect those tables.

To manage our data relationships, select Relationships view from the right hand side. Now we’ll see there’s no any relationship between our tables.


If we want our tables can interact each other, we have to create relationships between them. Let’s create the first relationship between the 12Aug table and the NotFixedPCs table. Select Manage Relationships from the Ribbon. Select New in the Manage Relationships window and choose a 1:1 relationship with both direction cross filter.


Now you can go back to Report view and try to click a country in the 12-Aug chart, you’ll see the filter affects the Not Fixed PCs chart. Create a similar relationship for the 12Aug table and the NewPCs table.

Now if we create a similar relationship for the 12Aug and the FixedPCs table, it’s more tricky. We can’t create a direct 1:1 relationship between them, because values of their index columns (columns with unique values: AssetID and Service Tag) are not match. The rest of columns don’t have unique values so can’t be used to make relationship. To make a Country Code filter in the 12Aug table have effect in the FixedPCs table, we need to have a middle table, which stores unique Country Code values.

Time to use a bit of DAX language to create that middle table. Go back to Data view, select Modeling tab in the ribbon and select New Table.


Type the below command in the new table command box:

Countries = DISTINCT(’12Aug'[CountryCode])

This DAX command create a new table named Countries with 1 column. Values of this column are distinct values from the column CountryCode of the 12Aug table. Press Enter and the new Countries table created!


Now we’ll relate the 3 tables together. Go to Relationships view, select Manage Relationships and create relationship between the 12Aug and the Countries tables. This is a *:1 relationship with Both direction cross filter.


A similar relationship will be created for the FixedPCs and the Countries tables. Finally, these are our data set relationship:

28_Total Relationship

Now the cross filter works for the Fixed PCs chart like a charm!

That’s all, we got a nice dashboard with a magical cross filter.

PC Encryption Dashboard – Part 1

PC Encryption Dashboard – Part 2

About dongthao

"Man does not simply exist but always decides what his existence will be, what he will become the next moment"
This entry was posted in Data Science, Software Tutorial and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s