Report Example within Power BI

Load Data

At first load data to a blank file. 1. Open Power BI Desktop. (The web application is not able to import data from an SQL Server.) 2. Select Import data from SQL Server to add data to your report.

Enter the Server name and the name of the database. You can find this information in the email with your access data. At next select DirectQuery and click OK.

Connection Mode DirectQuery: Creates a direct connection to the database. The data is always up to date because it comes directly from the source and is not a copy. If the data in the original source is changing also the used data is changing. Import: Stores the data from the source to the Power BI file. The used data gets old because it's only a copy of the original data. This mode can be faster in performance, but is not recommended to be used for a report, also caused by the huge file size.

1. In the Navigator select all the tables you need to create your report and click on Load. Only select the needed tables to keep your file clear. If you put the name of the schema (for example processes) into the search bar, only the tables of the regarding models are suggested. (Your access data are sent in the email containing the name of the SQL Schema.) There is an option to check just the main table (here: DWH__Demo_released_processes.fact_Processes) of a model and auto-select all related tables of this model by using the Select Related Tables-Button.

If the connection is created, you will find all loaded tables in the section Fields on the right panel:

Navigation in Power BI

On the right side there are three areas with different kinds of tools to work with. These areas, which also can be hidden, are called sections in the following (Filter, Visualizations and Fields):

Beside that you will find two or three different panes in the Visualizations-Section, on the left there is the Fields-Pane, in the middle the Format-Pane and on the right is the Analytics-Pane, which is only displayed if any visualization is selected.

Add Visualization

Now let's add a visualization to the report. 1. In the section Visualizations on the right side you can choose between different kinds. If you hover over the icons, you can see the name of the diagrams in the tooltip. To understand how Power BI works, let's start with a simple visualization, for example select Table.

Now a blank table is part of your report. At next populate it with data.

1. Select the new created table in the page on the left. And, on the right, change in the Visualizations-Section to the Fields-Pane. You can select the data from the Fields-Section on the right or drag the data field and drop it to the Values.

By drag and drop you can change the order of the fields. Depending on the chosen visualization there can be additional information where you need to add fields, for example for the legend.

1. Do it the same way for other visualizations.

Filter Data

To show only particular and not all contained data, you can filter the data in the Filter-Section. In the following example there are two versions of each process, one in English and one in German; the different languages are filtered based on the column CultureId.

Filter Area You can apply the filter only on the current visualization, on the whole page or on all pages. If you filter by language it makes sense to filter on the whole page and not only on one visualization.

Drag the column you want to filter into the Filter-Section and choose Basic filtering as Filter type.

Filter types Basic filtering: Selects from available values. Advanced filtering: Filters with operators and individual values.

Calculated Values

To display not only the values your tables provide you, but also calculated values, let’s create a measure with DAX.

Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. You can use DAX to solve a number of calculations and data analysis problems, which can help you create new information from data that is already in your model. Detailed Tutorial

1. At first select the data-table (in the Fields-Section) the measure relates to.

1. Click on New Measure in the Table Tools-Tab on the top:

1. An input appears on the top. Here you can name the measure and use it in your DAX. Select an expressive name, so you know what the measure is there for.

This example shows an easy calculation, that counts the number of processes by counting the rows of the process-table:

After creating the measure it is visible in the Fields-Section on the right:

1. The measure field can be used the same way as the imported data fields. In this example we only want to display the calculated number of processes without a chart. Therefor choose the table-visualization and add the created measure in the Fields-Pane as value:

After formatting the column header, the table looks like this:

Format Visualization

Next let's format the visualizations to give the report an expressive look. There are nearly endless options to customize the look of visualizations. For an individual design you can change parameters as color, font or size. But some units as the title can also be hidden. In the following you'll find an overview of the most important formatting options.

1. In order to change the format of a visualization select it in the page on the left and change to the Format-Pane in the Visualizations-Section on the right.

Visualization Designs General: You can change the position and add a description. Title: You can edit the style of the title, for example font color or alignment. Background: You can choose the background color and transparency. Border: You can add a border and edit its color and radius. Data Color: For charts you can select the color of the of the displayed data. Legend: For charts you can show or hide the legend and edit its style. Column Headers: You can design the column headers of a table, for example the colors of font and background. Grid: You can edit the grid of a table, for example you can change colors, text size or line weight. X/Y Axis: You can change for example the scale type, color, text size or category width of an axis.

1. In the first example let's add a title to the table and change its font color: Expend the Title-Card and change the slider from off to on. Here you can add a title and change its color or size:

1. But there are also more interesting kinds of visualizations: If you’ve added a bar chart, you can edit for example the format of the legend or the axis in the Format-Pane:

Which data is shown in the legend, you determine in the Fields-Pane, for example you can show the state of a process in this pie chart:

Page Formatting

To refine the outlooking of the report at last let’s change the formatting of the whole page.

1. If no visualization is selected, go to the Format-Pane, and expand Page Background. Here you can edit the color of the background. There is also the option to select a background picture.

1. Go to the Insert-Tab on the top in order to add elements as text boxes or pictures.

For example you can add a text box with a title or a logo of a company to the report: