With the modeling capabilities in Power BI, you can connect to multiple data sources, then combine them in ways that create a unique model of data that suits your needs.
Introduction to modeling your data
Welcome to the Modeling section of the Guided Learning course for Power BI. This section shows you how to get your connected data ready for use, using Power BI Desktop. Often, you'll connect to more than one data source to create your reports, and you'll need all of that data to work together. Modeling is how you get it there.
To create a logical connection between different data sources, you create a relationship. A relationship between data sources enables Power BI to know how those tables relate to one another, allowing you to create interesting visuals and reports. This section explains relationships (only the data-centric ones, though), and even shows you how to create relationships when none exists.
As always in this course, your learning journey follows the same path as the flow of work in Power BI. We'll still be in Power BI Desktop for most of this section, but the work done here has direct affect on working in the Power BI service.
Introduction to modeling your data
Now that we've reviewed how to import your data and transform it, it's time to start modeling.
One of Power BI's strengths is that you don't need to flatten your data into one table. Instead, you can use multiple tables from multiple sources, and define the relationship between them. You can also create your own custom calculations and assign new metrics to view specific segments of your data, and use these new measures in visualizations for easy modeling.
How to manage your data relationships
Power BI allows you to visually set the relationship between tables or elements. To see a diagrammatic view of your data, use the Relationship view, found on the far left side of the screen next to the Report canvas.
From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships.
Adding and removing relationships is simple. To remove a relationship, right-click on it and select Delete. To create a relationship, drag and drop the fields that you want to link between tables.
To hide a table or individual column from your report, right-click on it in the Relationship view and select Hide in Report View.
For a more detailed view of your data relationships, select Manage Relationships in the Home tab. This will open the Manage Relationships dialog, which displays your relationships as a list instead of a visual diagram. From here you can select Autodetect to find relationships in new or updated data. Select Edit in the Manage Relationships dialog to manually edit your relationships. This is also where you can find advanced options to set the Cardinality and Cross-filter direction of your relationships.
Your options for Cardinality are Many to One, and One to One. Many to One is the fact to dimension type relationship, for example a sales table with multiple rows per product being matched up with a table listing products in their own unique row. One to One is used often for linking single entries in reference tables.
By default, relationships will be set to cross-filter in both directions. Cross-filtering in just one direction limited some of the modeling capabilities in a relationship.
Setting accurate relationships between your data allows you to create complex calculations across multiple data elements.
Create calculated columns
Creating calculated columns is a simple way to enrich and enhance your data. A calculated column is a new column that you create by defining a calculation that transforms or combines two or more elements of existing data. For example, you can create a new column by combining two columns into one.
One useful reason for creating a calculated column is to establish a relationship between tables, when no unique fields exist that can be used to establish a relationship. The lack of a relationship becomes apparent when you create a simple table visual in Power BI Desktop, and you get the same value for all entries, yet you know the underlying data is different.
To create a relationship with unique fields in data, you can, for example, create a new calculated column for "Full Phone Number" by combining the values from the "Area Code" and "Local Number" columns when those values exist in your data. Calculated columns are a useful tool for quickly creating models and visualizations.
To create a calculated column, select the Data view in Power BI Desktop from the left side of the report canvas.
From the Modeling tab, select New Column. This will enable the formula bar where you can enter calculations using DAX (Data Analysis Expressions) language. DAX is a powerful formula language, also found in Excel, that lets you build robust calculations. As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.
The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.
Once the calculated columns are created in each table, they can be used as a unique key to establish a relationship between them. Going to Relationship view, you can then drag the field from one table to the other to create the relationship.
Returning to Report view, you now see a different value for each district.
There are all sorts of other things you can do by creating calculated columns, too.
Optimizing data models
Imported data often contains fields that you don't actually need for your reporting and visualization tasks, either because it's extra information, or because that data is already available in another column. Power BI Desktop has tools to optimize your data, and make it more usable for you to create reports and visuals, and for viewing your shared reports.
To hide a column in the Fields pane of Power BI Desktop, right-click on it and select Hide. Note that your hidden columns are not deleted; if you've used that field in existing visualizations, the data is still in that visual, and you can still use that data in other visualizations too, the hidden field just isn't displayed in the Fields pane.
If you view tables in the Relationships view, hidden fields are indicated by being grayed out. Again, their data is still available and is still part of the model, they're just hidden from view. You can always unhide any field that has been hidden by right-clicking the field, and selecting unhide.
Sorting visualization data by another field
The Sort by Column tool, available in the Modeling tab, is very useful to ensure that your data is displayed in the order you intended.
As a common example, data that includes the name of the month is sorted alphabetically by default, so for example, "August" appears before "February".
In this case, selecting the field in the Fields list, then selecting Sort By Column from the Modeling tab and then choosing a field to sort by can remedy the problem. In this case, the "MonthNo" category sort option orders the months as intended.
Setting the data type for a field is another way to optimize your information so it's handled correctly. To change a data type from the report canvas, select the column in the Fields pane, and then use the Format drop-down menu to select one of the formatting options. Any visuals you've created that display that field are updated automatically.
Create calculated measures
A measure is a calculation that exists in your Power BI data model. To create a measure, in Report view select New Measure from the Modeling tab.
One of the great things about DAX, the Data Analysis Expression language in Power BI, is that it has lots of useful functions, particularly around time-based calculations such as Year to Date or Year Over Year. With DAX you can define a measure of time once, and then slice it by as many different fields as you want from your data model.
In Power BI, a defined calculation is called a measure. To create a measure, select New Measure from the Home tab. This opens the Formula bar where you can enter the DAX expression that defines your measure. As you type, Power BI suggests relevant DAX functions and data fields as you enter your calculation, and you'll also get a tooltip explaining some of the syntax and function parameters.
If your calculation is particularly long, you can add extra line breaks in the Expression Editor by typing ALT-Enter.
Once you've created a new measure, it will appear in one of the tables on the Fields pane, found on the right side of the screen. Power BI inserts the new measure into whichever table you have currently selected, and while it doesn't matter exactly where the measure is in your data, you can easily move it by selecting the measure and using the Home Table drop-down menu.
You can use a measure like any other table column: just drag and drop it onto the report canvas or visualization fields. Measures also integrate seamlessly with slicers, segmenting your data on the fly, which means you can define a measure once, and use it in many different visualizations.
The Calculate DAX function is a powerful function that enables all sorts of useful calculations, which is especially useful for financial reporting and visuals.
Create calculated tables
Calculated tables are a function within DAX that allows you to express a whole range of new modeling capabilities. For example, if you want to do different types of merge joins or create new tables on the fly based on the results of a functional formula, calculated tables are the way to accomplish that.
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 on the left side of the equal sign, and the calculation that you want to use to form that table on the right. When you're finished your calculation, the new table appears in the Fields pane in your model.
Once created, you can use your calculated table as you would any other table in relationships, formulas, and reports.
Explore your time-based data
It's easy to analyze time-based data with Power BI. The modeling tools in Power BI Desktop automatically include generated fields that let you drill down through years, quarters, months, and days with a single click.
When you create a table visualization in your report 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, as shown in the following image.
Visualizations 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.
Now when you click on the bars or lines in your chart, it drills 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, which in this example is days. To move back up through the time hierarchy, click on Drill Up in the top left-hand corner of the visual.
You can also drill down through all of the data shown on the visual, rather than one selected period, by using the Drill All double-arrow icon, also in the top right-hand corner of the visual.
As long as your model has a date field, Power BI will automatically generate different views for different time hierarchies.
To get back to individual dates rather than using the date hierarchy, simply right-click the column name in the Fields well (in the following image, the name of the column is InvoiceDate), then select the column name from the menu that appears, rather than Date Hierarchy. Your visual then shows the data based on that column data, without using the date hierarchy. Need to go back to using the date hierarchy? No problem - just right-click again and select Date Hierarchy from the menu.
Congratulations! You've completed this section of the Guided Learning course for Power BI. Now that you know about modeling data, you're ready to learn about the fun stuff waiting in the next section: Visualizations.
As mentioned before, this course builds your knowledge by following the common flow of work in Power BI:
- Bring data into Power BI Desktop, and create a report.
- Publish to the Power BI service, where you create new visualizations and build dashboards
- Share your dashboards with others, especially people who are on the go
- View and interact with shared dashboards and reports in Power BI Mobile apps
While you might not do all that work yourself, you'll understand how those dashboards were created, and how they connected to the data... and when you're done with this course, you'll be able to create one of your own.
See you in the next section!
You are on a roll. Keep up the good work in learning about Power BI! You've completed the Modeling section of Microsoft Power BI Guided learning. Next up is visualizations.