Creating stunning visualizations in Power BI is amazingly easy but the correctness of values must be ensured so that legitimate information is conveyed.  In our last Power BI training in Sydney, we found many attendees creating tables which had misleading values. So we decided to write a step-by-step guide on dealing with misleading interpretations. Here’s the link to the blog series covering all such problems: https://powerbitraining.com.au/category/data-modeling-issues-and-solutions/ In this blog post, a step-by-step guide on dealing with misleading interpretations is presented but let us summarize the concepts covered so far:

1. Importing Data

Importing data refers to bringing in the data required into the Power BI desktop. This data is then published to the service and the data base is configured to create a live connection using the Power BI gateway. 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

Once the data in imported into the desktop file, it is cleaned and transformed into a format which is suitable for data analysis. This is known as data preparation. 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 table is one of the dimension table in the data model. The table contains date and is used to analyze data by dates. Date table can be created using DAX functions such as CALENDAR() and CALENDARAUTO(). 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

This stage refers to creating relationships between the different tables in the data model. This is done using the primary and foreign keys. 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

Sometimes the tables cannot be linked due to the absence of primary key column which acts as a row identifier. These keys are known as surrogate key and must be created. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/

6. Merging Queries

After the surrogate key is created, it is used to merge tables containing similar data. For a step by step guide on this:  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 on the top of data model to enrich the data and add information to it. Calculated columns help in answering the business question at hand by bridging the information gap. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/

8. Flattening the Product Table

Fattening is the process of converting multiple related dimension tables into one entity. These tables are often referred as the strings hanging of a snowflake schema. The flattening of the table refers to converting the snowflake schema into star schema. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/

 9. Creating Product Hierarchy

Hierarchy in the data can be represented in Power BI only if all the columns representing the hierarchy are present in the same table. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/

10. Active vs. Inactive Relationship

Sometimes two fields in the same table need another dimension for complete representation but at any instant only one active relationship can exist between the two tables. An example of this problem is the existence of two date fields such as order date and shipping date in the sales table. Both create a relationship with the date table but only one active relationship exists at one time. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/

11. Working with Role Playing Dimensions

Role playing dimensions are the dimensions such as the date which can have multiple relationships with the other tables. However, only one active relationship can exist between the two table at any one time. Therefore, the dimension itself plays a role in the analysis. For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/

12. Adding Higher Levels of Details

It is observed that once a report or a dashboard is brought to production, some new requirements emerge and often these requirements demand the bringing inn of more data thereby adding more details to the data model. For a step by step guide on this:  https://powerbitraining.com.au/working-with-multiple-fact-tables-power-bi-training-in-sydney-power-bi-training-in-melbourne/

13. Cater for Misleading Interpretations

In the last blog post, we created two tables for visualization. Both of the tables showed results but the results were misleading in one table. Now you will learn how to use DAX to avoid any discrepancy in your report visualization. To Cater for the Misleading Interpretations: 1. Click on Sales Target Table in the Fields Pane. 2. Click on New Measure. 3. Type the following DAX expression in the Formula Bar.