How to manage your data relationships



ModelingWelcome 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.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 relationshipsPower 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 columnsCreating 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 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.Optimizing data modelsImported 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 fieldThe?Sort by Column?tool, available in the?Modeling?tab, is very useful to ensure that your data is displayed in the order you 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 measures0-1270A?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.Create calculated tablesCalculated 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 barType 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 dataIt'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.Next stepsCongratulations!?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 dashboardsShare?your dashboards with others, especially people who are on the goView and interact with shared dashboards and reports in?Power BI Mobile?apps ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download