Calculated tables is a function within DAX. Most of the time, you can import data into your model from an external data source. However, calculated tables provide intermediate calculations and data that you want stored as part of the model rather than as part of a query. You can use calculated tables, for example, to cross join two tables.
Tasks in this module include:
Video: Create calculated measures
To create a calculated table, go to Data view in Power BI Desktop, which you can activate from the left side of the report canvas. Select New Table from the Modeling tab to open the formula bar.
Type the name of your new table, the equal sign, and the calculation that you want to use to form the table. Your new table will appear on the Fields pane in your model.
After the new table has been created, you can use your calculated table as you would any other table in relationships, formulas, and reports.
Analyzing time-based data with Power BI is a simple process. The modeling tools in Power BI Desktop automatically generate fields that let you drill down through time periods.
Tasks in this module include:
Video: Explore time based data
When you create a table visualization in your report by using a date field, Power BI Desktop automatically includes breakdowns by time period. For example, the single date field in the Date table was automatically separated into Year, Quarter, Month, and Day by Power BI.
Visuals display data at the year level by default, but you can change that by turning on Drill Down in the top, right-hand corner of the visual.
When you select the bars or lines in your chart, the system will drill down to the next level of time hierarchy, for example, from years to quarters. You can continue to drill down until you reach the most granular level of the hierarchy: days. To move back up through the time hierarchy, select Drill Up in the top, left-hand corner of the visual.
You can also drill down through all the data that is shown on the visual instead of through one selected period. To do so, use the Go to the next level in the hierarchy double-arrow icon.
As long as your model has a date field, Power BI will automatically generate different views for different time hierarchies.
Visuals allow you to present data in a compelling and insightful way, and help you show the important components of it. Power BI has many compelling visuals and many more that are released frequently.
The following are the tasks that you’ll complete in this chapter:
Explore Power BI visuals
Video: Visuals in Power BI
This unit begins with the mainstays of visualizations, the simple visuals that everyone’s familiar with, to make sure that you know the particulars of them. The rest of the module will provide more advanced, or at least less common, details to enhance your report-creating knowledge.
Visualizing data is one of the core parts and basic building blocks of Power BI. Creating visuals is one of the most effective ways to find and share your insights.
You’ll discover a wide variety of visualizations in Power BI, which offers features such as simple bar charts, pie charts, maps, and more esoteric offerings like waterfalls, funnels, and gauges. Power BI Desktop also offers extensive page formatting tools, such as shapes and images, that help bring your report to life.
This unit explains how to create new bar charts, pie charts, and tree maps, and how to customize these elements to suit your reports.
Video: Create simple visuals
Two ways to create a new visualization in Power BI Desktop are:
Drag field names from the Fields pane and then drop them on the report canvas. By default, your visualization appears as a table of data.
In the Visualizations pane, select the type of visualization that you want to create. With this method, the default visual is a blank placeholder that resembles the type of visual that you selected.
After you have created your graph, map, or chart, you can begin dragging data fields onto the bottom portion of the Visualization pane to build and organize your visual. The available fields will change based on the type of visualization that you selected. As you drag and drop data fields, your visualization will automatically update to reflect changes.
You can resize your visual by selecting it and then dragging the handles in or out. You can also move your visualization anywhere on the canvas by selecting and then dragging it to where you want it. If you want to convert between different types of visuals, select the visual that you want to change and select a different visual from the Visualization pane. Power BI attempts to convert your selected fields to the new visual type as closely as possible.
As you hover over parts of your visuals, you’ll receive a tooltip that contains details about that segment, such as labels and total value.
Select the paintbrush icon on the Visualizations pane to make cosmetic changes to your visual. Examples of cosmetic changes include background alignment, title text, and data colors.
The available options for cosmetic changes to your visual vary depending on the type of visual that you’ve selected.
Note: Generally, visuals are used to compare two or more different values. However, sometimes when you are building reports, you might want to track a single metric over time. For more information, see Radial gauge charts in Power BI
Create combination charts
Combination charts are an effective way to visualize multiple measures that have different scales in a single visualization.
Video: Combination charts
You might want to visualize two measures with different scales, such as revenue and units. Use a combination chart to show a line and a bar with different axis scales. Power BI supports many different types of combination charts by default, including Line and Stacked Columns charts.
You can split each column by category by dragging a category into the Column Series field. When you do so, each bar is proportionately colored based on the values within each category.
Power BI Desktop is a free application for PCs that lets you gather, transform, and visualize your data. In this module, you’ll learn how to find and collect data from different sources and how to clean or transform it. You’ll also learn tricks to make data-gathering easier.
Power BI Desktop and the Power BI Service work together. You can create your reports and dashboards in Power BI Desktop, and then publish them to the Power BI Service for others to consume.
The following are the tasks that you will complete in this module:
Video: Introduction to Power BI Desktop
To perform the exercises in this module, you’ll need to have Power BI desktop installed and have a Power BI Service account set up.
Download Power BI Desktop
You can download Power BI Desktop from the web or as an app from the Microsoft Store on the Windows tab.
Slicers are one of the most powerful types of visualizations, particularly as part of a busy report. A slicer is an on-canvas visual filter that allows report users to segment the data by a specific value. Examples of filters include by year or by geographical location.
Video: Create slicers
To add a slicer to your report, select Slicer from the Visualizations pane.
Drag the field by which you want to slice and drop it to the top of the slicer placeholder. The visualization turns into a list of elements with check boxes. These elements are your filters. Select the box next to the one that you want to segment, and Power BI will filter, or slice, all other visuals on the same report page.
A few different options are available to help you format your slicer. You can set it to accept multiple inputs at once, or you can use the Single Select mode to use one at a time. You can also add a Select All option to your slicer elements, which is helpful when you have a long list. Change the orientation of your slicer from the vertical default to horizontal, and it becomes a selection bar rather than a checklist.
The idea of building and sharing reports is an abstract concept. It will make more sense if you explore Power BI Desktop hands-on. The first step is to launch and explore the user interface (UI).
In this chapter, you will:
Launch the Power BI Desktop.
Explore the UI.
Video: Overview of Power BI Desktop and the Power BI service
Launch Power BI Desktop
When you launch Power BI Desktop, the Getting Started dialog box will appear, which provides useful links to forums, blogs, and introductory videos. Close this dialog box for now, but keep the Show this screen on startup option selected so that you can explore it later.
Explore the report building environment
In Power BI Desktop, you’ll begin to build reports in the Report view. You’ll be working in five main areas:
Ribbon – Displays common tasks that are associated with reports and visualizations.
Report view, or canvas – Where visualizations are created and arranged. You can switch between Report, Data, and Model views by selecting the icons in the left column.
Pages tab – Located along the bottom of the page, this area is where you would select or add a report page.
Visualizations pane – Where you can change visualizations, customize colors or axes, apply filters, drag fields, and more.
Fields pane – Where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.
Tip: You can collapse the Visualizations and Fields panes to provide more space in the Report view by selecting the small arrow, as shown in the following screenshot.
Create a visual
To create a visual, drag a field from the Fields list onto the Report view.
Power BI Desktop automatically created a map-based visualization because it recognized that the State field contained geo-location data.
Publish a report
After creating a report with a few visuals, you’re ready to publish to the Power BI service. On the Home ribbon on the Power BI Desktop, select Publish.
You’ll be prompted to sign in to Power BI. When you’ve signed in and the publish process is complete, the following dialog box will appear. You can select the link below Success!, which will take you to the Power BI service, where you can see the report that you published.
Pin a visual to a dashboard
When you view a published report in the Power BI service, you can choose the Pin icon to pin that visual to a dashboard.
You can choose whether to pin the visual to an existing dashboard or to create a new dashboard.
Power BI has two different types of map visualizations: a bubble map that places a bubble over a geographic point, and a shape map that shows the outline of the area that you want to visualize.
Video: Map visuals
Important: When you are working with countries or regions, use the three-letter abbreviation to ensure that geocoding works properly. Do not use two-letter abbreviations because some countries or regions might not be properly recognized.
If you only have two-letter abbreviations, go to this external blog post for steps on how to associate your two-letter country and/or region abbreviations with three-letter country and/or region abbreviations.
Create bubble maps
To create a bubble map, select the Map option in the Visualization pane. In the Visualizations options, add a value to the Location bucket to use a map visual.
Power BI accepts many types of location values. It recognizes city names, airport codes, or specific latitude and longitude data. Add a field to the Size bucket to change the size of the bubble for each map location.
Create shape maps
To create a shape map, select the Filled Map option in the Visualization pane. As with bubble maps, you must add a value to the Location bucket to use this visual. Add a field to the Size bucket to change the intensity of the fill color.
A warning icon in the top-left corner of your visual indicates that the map needs more location data to accurately plot values. This is a common problem when the data in your location field is ambiguous, such as using an area name like Washington, which could indicate a state or a district.
One way to resolve the location data problem is to rename your column to be more specific, such as State. Another way is to manually reset the data category by selecting Data Category on the Modeling tab. From the Data Category list, you can assign a category to your data such as “State” or “City.”
Power BI Desktop connects to many types of data sources, including local databases, worksheets, and data on cloud services. Sometimes when you gather data, it’s not quite as structured, or clean, as you want it to be. To structure data, you can transform it, meaning that you can split and rename columns, change data types, and create relationships between columns.
In this chapter, you will:
Connect to data.
Import data into Power BI Desktop.
Video: Connecting to data sources
You can connect Power BI Desktop to many types of data sources, including on-premises databases, Microsoft Excel workbooks, and cloud services. Currently, there are about 60 Power BI-specific connectors to cloud services such as GitHub and Marketo. You can also connect to generic sources through XML, CSV, text, and ODBC. Power BI will even extract tabular data directly from a website URL.
Connect to data
When you start Power BI Desktop, you can choose Get Data from the ribbon on the Home tab.
In Power BI Desktop, several types of data sources are available. Select a source to establish a connection. Depending on your selection, you’ll be asked to find the source on your computer or network. You might be prompted to sign in to a service to authenticate your request.
Choose data to import
After connecting, the first window that you’ll see is the Navigator. The Navigator window displays the tables or entities of your data source, and selecting a table or entity gives you a preview of its contents. You can then import your selected tables or entities immediately, or you can select Edit to transform and clean your data before importing.
After you’ve selected the tables that you’d like to bring into Power BI Desktop, select the Load button. You might want to make changes to those tables before you load them. For example, if you only want a subset of customers or a specific country or region, select the Edit button and filter data before loading.
No matter what type of data you need, you’re likely to find a way to import it into Power BI Desktop.
You can use Power BI Desktop to create graphical and tabular visuals.
Video: Create tables
If you have numerical information in a table, such as revenue, a total sum will appear at the bottom. You can manually sort by each column by selecting the column header to switch ascending or descending order. If a column isn’t wide enough to display all its contents, select and drag the column header to expand it.
In the Visualizations pane, the order of the fields in the Values bucket determines the order in which they appear in your table.
A matrix is similar to a table, but it has different category headers on the columns and rows. As with tables, numerical information will be automatically totaled along the bottom and right side of the matrix.
Many cosmetic options are available for matrices, such as auto-sizing columns, switching between row and column totals, setting colors, and more.