One of the most interesting things about Power BI is that it covers a wide range of areas. Therefore, it can help a wide range of different users to analyse and understand their businesses easily. For instance system administrators can use Power BI to analyse their Microsoft Windows Active Directory. As a matter of fact, Power BI and Active Directory can work together very nicely so that a system administrator can create high level reports and dashboards.

In this , we’ll create a report of the following charts:

Total number of computers by Operating System/Service Pack

Total number of computers by year and Operating System

Total number of computers

Print pages per minute by printer

Total number of printers by year and driver name

As a system administrator you can create heaps of other useful reports.

Get Data

On Power BI Desktop click “Get Data” then click “More”

Click “Other”, click “Active Directory” then click “Connect”

Enter a Domain name then click OK

As you can see there are 374 tables you can select to create heaps of reports. In this post I use “Computer” and “PrintQueue”

After selecting the desired tables you can either click “Load” or click “Edit” to modify the queries using Query Editor. I click “Load”.

Expand both tables from “Fields” pane

As you can see both tables have just two columns which don’t look to be helpful. So to get some more informative data out of those tables we need to do some works in the next steps.

Click on “Edit Queries” from the ribbon

As you can see Power Query engine detected lots of related columns with an expand icon ( ) next to them. These columns, are called Complex Columns. Complex columns are basically the columns that do not exist in the original table, but, exist in a related table. We can expand the complex columns to reveal the containing values.

next to them. These columns, are called Complex Columns. Complex columns are basically the columns that do not exist in the original table, but, exist in a related table. We can expand the complex columns to reveal the containing values. Click on “Computer” table from the “Queries” pane

Click on the expand button of the “computer” column

Scroll down the list and tick the following columns: OperatingSystem OperatingSystemServicePack OperatingSystemVersion



As you can see you can search for desired column names

You can unselect the “The Original Column Name as Prefix” if you don’t need it. Click OK.

As you can see there are some rows with Null value in OperatingSystem column. We are not interested on those rows. So we can simply filter the table to hide the rows of data with Null on OperatingSystem.

We also need to expant the column “top” and select “CreateTimeStamp” then click OK

In our sample we need to have Year part of the “CreateTimeStamp” column. To extract Year, click on Date-> Year-> Year from “Transform” tab

Click on “PrintQueue” from the Queries pane

Expand the “PrintQueue” column with the following columns then click OK DriverName PrintMaxResolutionSupported PrintPagesPerMinute



We also need to expand the “top” column and select “CreateTimeStamp” column

Extract Year part of the “CreateTimeStamp” column. We discussed how to extract Year part in previous steps.

Now we have everything we need to create our report. Click on “Close & Load” button from the ribbon

Create Reports

Total Number of computers by Operating System/Service Pack

Switch to Report view

Click on column chart from “Visualisation” pane

From “Fields” pane drag and drop “OperatingSystem” on Axis area

Drag and drop “DisplayName” on Value area

Make sure that “Count” is selected as aggregation for Value

Switch “Data Labels” to ON from format tab

Drag and drop “OperatingSystemServicePack” on the Legend area

You can replace chart name and titles by something more meaningful. You can also change the look and feel of the chart. I leave these changes to you. If you are new to Power BI Desktop I encourage you to read this post to learn how to create data visualisations in Power BI Desktop on a step-by-step basis.

Total Number of Computers by Year and Operating System

From Fields pane expand “Computer” table

Drag and drop “CreateTimeStamp” on the report area

Move it from Value are to Axis area

Select “OperatingSystem” and “DisplayName” respectively

Change the chart type to Area Chart

Change the Legend Position to Right

Switch “Data Labels” to ON

Total Number of Computers

This one is the easy one. You just need to expand “Computer” table from the Fields pane then click on “DisplayName” then change the chart type to Card.

I don’t like the chart name which is “Count of displayName”. If you click on the Format tab from Visualisation pane you’ll see there is no format settings for this type of chart. Although you cannot change the chart title from Power BI Desktop you can change it when you published it to Power BI Website. I’ll explain how to this later in this post.

If you don’t want to publish the report to the Power BI Website for now, a workaround is to rename the corresponding column to something more meaningful.

I personally don’t think renaming the column is the right way to modify the chart’s title. So I hope Microsoft adds this feature in the next versions of the product.

Print Pages Per Minute by Printer

Click Waterfal Chart from the Visualisation pane

Expand “PrintQueue” table from the Fields pane

Put “DriverName” on Axis

Put “PrintPagesPerMinute” on Value

Put “PrintPagesPerMinut” on Colour Saturation area

Go to Format

From Data Colours switch “Diverging” to ON

Set Minimum colour to Red

Set Centre colour to Blue

Set Maximum colour to Green

Total Number of Printers by Year and Driver Name

Click on Table from Visualisation pane

From Fields pane click “Driver Name”

Click “CreateTimeStamp”

Select “Do Not Summarise”

Drag and drop the “Driver Name” to the Values area again

Select “Count” for the aggregation

Resize the table

Publish the report to Power BI Website

We created some reports and it’s time to publish the reports to Power BI Website. It’s super easy to publish the reports. You just need to click Publish from the ribbon and pass your Power BI credentials.

Create Dashboards on Power BI Website

Open your browser and login to your Power BI account

Make sure that report is published correctly by clicking on the report

Now create a new dashboard and name it Active Directory

Click on the report again and pin the charts to the dashboard

Click on the “Active Directory” from the dashboards again

As you can see you have all the charts on the dashboard

Now you just need to reorganise the dashboard as desired

Changing a Chart Title

As I stated before in some charts we cannot modify the chart title. But, luckily we can change them from the dashboard.

Open the dashboard

When you hover over a chart a “Title Details” ( )and “Delete Title” ( ) buttons appear.

)and “Delete Title” ( ) buttons appear. Click on “Title Details”

Enter a desired title then click “Apply”

As you can see, we are actually modifying a “Tile Title” not a chart title. Each space on the dashboard that we put the dashboard objects on it is a tile. Each tile can have a title which is a chart title by default. We can also setup a custom link (URL) for a tile. So if users click on a tile they will be redirected to the URL.

Like this: Like Loading...