Live Power BI training uncovers many run time issues. These common issues are addressed at the spot by our Microsoft Certified trainers. This is why we recommend students to attend live Power BI training in Sydney, Melbourne, Brisbane, Perth, Adelaide, Canberra or any other location across Australia. However, if you’ve not attended any of our Power BI training in Sydney, Melbourne, Brisbane, Perth, Adelaide, Canberra or any other location then here’s a link to the archive covering common issues and the solutions in Data Modeling:
https://powerbitraining.com.au/category/data-modeling-issues-and-solutions/
Let’s summarize the concepts covered so far:
1. Importing Data
The Importing Data phase of a data modeling involves procuring the right tables that can answer the business question at hand.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/
2. Data Preparation
Data Preparation involves ensuring that the data is capable of delivering value. Data Preparation involves checking the data types and data categories and making sure that all the required columns are present in data set. Power BI guesses the data type of the column based on top 1000 Rows.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/
3. Creating Date Table
Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/
4. Setting Model Design
The model design consists of creating relationship between the data tables procured for the task at hand.
Now that we have linked all the other tables and we do not find a key column to link the Sales Person table. Therefore, no relationship has been created for Sales Person table.
For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/
5. Creating Surrogate Key
Surrogate Key links the isolated Sales Person Table to the existing Data Model. Creating a surrogate key in a table where it does not exist and then linking the Sales Person Table to the Sales table using this key is also a part of data modeling. Once the Surrogate Key is created the isolated table can be linked to the data model.
For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/
6. Merging Queries
The Person ID column is still missing in the Sales Table. In order to create a relationship between Sales Person and Sales table, Person ID must be present in Both Tables. Therefore, we will use Merge query to add Person ID column in Sales Table. The data model is complete as seen in the image below:
For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/
7. Creating Calculated Columns
Calculated Columns are created to provide you with insights related to your data set and make data visualization easy and meaningful.
In this exercise, you will use RELATED() and RELATEDTABLE() functions and learn how to use Nested Functions.
To Create Calculated Columns:
1. Click on Sales Tables in the Fields.
2. Click on New Column in the Calculations Section.
3. Type the following DAX expression in the Formula Bar.
A new column is created in the sales table.
This column lists the related Product Type from the Product Line Prod Type Table. However, the Product Type for any transaction can be easily found as a relationship exists between these two tables. Therefore, the RELATED() function has not been put to good use.
4. Click on three dots at the right of Subcategory Column. Click Delete.
Now we use the RELATED() column to find the Discount Amount.
5. Click on New Column in the Calculations Section and Type the following DAX expression in the Formula bar.
A new column listing the discount associated with each transaction.
It is a good use of related function. It can be seen that almost no discount was associated with F18 product code. While some of the products were sold at a higher price and others at a lower price.
Now we use the RELATEDTABLE() function.
6. Click on Product line Prod Type Table in the Field Pane.
7. Click on New Column in the Calculations Section and Type the following DAX expression in the Formula bar.
This function performs a count calculation on the Sales table.
This is also not a good use of calculated columns. The same calculations can be performed on the report using the relationships instead of creating a separate column as it increases the processing load on Model.
8. Click on three dots at the right of Number of Transactions Column. Click Delete.
9. Click on Sales Person Table in the Field Pane.
10. Click on New Column in the Calculations Section and Type the following DAX expression in the Formula bar.
This is an example of use of Nested functions for calculations. CONCATINATE() is called inside another CONCATINATE().
Conclusion:
The steps involved in creating a typical data model are importing data, data preparation, creating date table, setting model design. One of the common issue that occurs while creating the data table is the missing surrogate key and missing link between the tables. Once these initial issues are solved, the next step is enriching the data model with the required data. In this blog post, the missing calculated tables are created.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia.
