Power BI – Data Modeling ”; Previous Next In this chapter, you will learn about data modeling in Power BI. Using Data Modeling and Navigation Data Modeling is one of the features used to connect multiple data sources in BI tool using a relationship. A relationship defines how data sources are connected with each other and you can create interesting data visualizations on multiple data sources. With the modeling feature, you can build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics. In the above image, you can see a common data model, which shows a relationship between two tables. Both tables are joined using a column name “Id”. Similarly, in Power BI, you set the relationship between two objects. To set the relationship, you have to drag a line between the common columns. You can also view the “Relationship” in a data model in Power BI. To create data model in Power BI, you need to add all data sources in Power BI new report option. To add a data source, go to the Get data option. Then, select the data source you want to connect and click the Connect button. Once you add a data source, it is presented on the right side bar. In the following image, we have used 2 xls file to import data – Customer and Product. In Power BI on the left side of the screen, you have the following three tabs − Report Data Relationships When you navigate to the Report tab, you can see a dashboard and a chart selected for data visualization. You can select different chart types as per your need. In our example, we have selected a Table type from available Visualizations. When you go to the Data tab, you can see all the data as per the defined Relationship from the data sources. In the Relationship tab, you can see the relationship between data sources. When you add multiple data sources to Power BI visualization, the tool automatically tries to detect the relationship between the columns. When you navigate to the Relationship tab, you can view the relationship. You can also create a Relationship between the columns using Create Relationships option. You can also add and remove relationships in data visualization. To remove a relationship, you have to right-click and select the “Delete” option. To create a new “Relationship”, you just need to drag and drop the fields that you want to link between the data sources. You can also use the Relationship view to hide a particular column in the report. To hide a column, right-click on the column name and select the “Hide in report view” option. Creating Calculated Columns You can create calculated columns in Power BI by combining two or more elements of the existing data. You can also apply calculation on an existing column to define a new metric or combine two columns to create one new column. You can even create a calculated column to establish a relationship between the tables and it can also be used to setup a relationship between two tables. To create a new calculated column, navigate to Data View tab on the left side of the screen and then click Modeling. When you navigate to the Modeling tab, you can see a New Column option at the top of the screen. This also opens the formula bar, where you can enter DAX formula to perform calculation. DAX- Data Analysis Expression is a powerful language also used in Excel to perform calculations. You can also rename the column by changing the Column text in the formula bar. In the following example, let us create a new column: Product Code (Product_C), which is derived from the last three characters of Prod_Id column. Then, write the following formula − Product_C = RIGHT( Sheet1[Prod_Id],3) A long list of formulas is also provided that you can use for creating calculated columns. You have to enter the first character of formula to be used in calculations as shown in the following screenshot. Creating Calculated Tables You can also create a new calculated table in data modeling in Power BI. To create a new table, navigate to the Data View tab on the left side of the screen, and then go to the Modeling option at the top of the screen. DAX expression is used to create the new table. You have to enter the name of a new table on the left side of the equal sign and DAX formula to perform the calculation to form that table on the right. When the calculation is complete, the new table appears in the Fields pane in your model. In the following example, let us define a new table – Table_CustC that returns a one column table containing unique values in a column in another table. A new table is added under the “Fields” section in Power BI screen as shown in the following screenshot. Once the calculated column and calculated tables are created as per your requirement, you can use the fields in the Report tab in Power BI. To add these objects, you have to select a checkbox and a relationship is automatically detected if possible. If not, then you can drag the columns that you want to connect. To view the report, you navigate to the Report tab and you can see both “Calculated columns” and fields from the new “Calculated table” in the report view. Managing Time-Based Data Power BI allows to drill through time-based data by default. When you add a date field in your analysis and enable drill on your data visualization, it takes you to the next level of time-based data. Let us consider we have added Time-based table in Power BI visualization. We have added Revenue and Year column in our report. We can enable the drill feature in
Category: power Bi
Power BI – Dashboard Options
Power BI – Dashboard Options ”; Previous Next In this chapter, you will learn about the various dashboard options available in Power BI. Exploring Different Datasets Power BI tool provides a lot of options to explore the datasets. When you are working on your BI report or dashboards, you can use Power BI look for quick insights. Navigate to the datasets section on the left side of the tool UI, click the 3 dots (…) and click Get Insights. When you select the Get Insights option, it runs all the algorithms in your dataset and once it is complete, you get a notification that insight is ready for your dataset. You can click the View Insights option and the tool will show you all chart representations of your data insights. You can any time go to this option and check insights option in your dataset. When you publish a report to Power BI service, you also get an option of Quick Insight on the first page. Creating Dashboards In Power BI, you can create a dashboard by pinning visualizations from BI reports that are published using Power BI desktop. All the visualizations that are created using Power BI service are also available for pinning to create dashboards. In Power BI, if you want to pin a visual, open the BI report on the Power BI service. At the top of the visual, select the pin icon. When you use the Pin option as shown as shown in the above screenshot, a new dialog box appears as shown in the following screenshot. It asks you to create a new dashboard or select an existing dashboard to put the visual from the dropdown list. If you don’t have an existing dashboard, then this option is greyed out. Once you click the Pin button, you will get a confirmation that your visualization is “Pinned” to the dashboard. You can click My Workspace and check the dashboard. Once your dashboard is created, you can use different options to configure the dashboard. Sharing Dashboards When you publish your BI report to Power BI service, you can share reports and dashboards with other users in your organization. Sharing a dashboard is very easy in Power BI. You have to open the dashboard in Power BI service and click the Share option at the top right corner of the screen. The sharing feature is only available with Power BI Pro version. You can also use the 60-days free trial for Power BI Pro as shown in the following screenshot. Click the Try Pro for free to start a trial. Select the Start Trial and Finish, and you will get a confirmation that 60-days trial has started. When you click the Share dashboard, you will get options in a new window. You have to enter the email Id of the user with whom you want to share this dashboard. You can allow recipients to share the dashboard with other users or send email notifications. You can also provide a direct URL to users and they can access the dashboard directly. Tiles in Dashboard When you check More Options in the dashboard, you can see an option of Focus Mode and other different options in the dashboard. Focus Mode is used to take a closer look at your dashboard data. When you have multiple values in the dashboard, you can use the Focus Mode for a better view of the objects in the dashboard. If there are any columns, which are not shown due to space issues, you can also view those using the Focus Mode. Focus Mode is used to see all the data in the dashboard/report. It is also possible to pin the visual directly from Focus Mode to a different dashboard by selecting the Pin icon. To exit the Focus Mode, you can select the Exit Focus Mode option. You can also use the Tile Details option to edit few formatting changes. This option allows to change the tile”s title, subtitle, last refresh time and date, and other details, such as creating a custom link for your dashboard. Data Gateway You can connect on-premise data sources to Power BI service using a data gateway. You can also use a version of data gateway that doesn’t include any administration configuration and it is called as Personal Gateway. You can set up Personal Gateway by logging into Power BI service. You have to select the download icon on the top right hand corner of the screen and click Data Gateway. As per Power BI official site, using Data Gateway with the on-premises gateways, you can keep your data fresh by connecting to your on-premises data sources without the need to move the data. Query large datasets and benefit from your existing investments. The gateways provide the flexibility you need to meet individual needs, and the needs of your organization. To set up download gateways, you have to run the setup till it is downloaded and the installation wizard is complete. You have an option to select either − On-premise data gateway or Personal gateway (Power BI only) Once you launch Power BI Gateway, you have to login to Power BI gateway service. You can also enable automatic updates by navigating to Schedule Refresh and frequency of the schedule. Print Page Previous Next Advertisements ”;