Dealing with misleading interpretations Power BI training in Melbourne

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.

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.

Here is a step-by-step guide on this Getting the right number of tables for a good data model 

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.

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().

Here is a step-by-step guide on Creating a very basic Date table in a Data Model

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.

Here is a step by step guide on Setting the Model Design in a Data Model

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.

Here is a step by step guide on Creating Surrogate Key in a data table  

6. Merging Queries

After the surrogate key is created, it is used to merge tables containing similar data.

Here is a step by step guide on Using Merge Feature to create key columns in Power BI  

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.

Here is a step by step guide on Enriching Data in a Data Model

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.

Here is a step-by-step guide on Optimizing Data Model

 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.

Here is a step-by-step guide on Creating Product Hierarchy in a Table

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.

Here is a step by step guide on Active vs. Inactive Relationship in a Data Model

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.

Here is a step-by-step guide on How to work with Role Playing Dimensions 

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.

Here is a step-by-step guide on Working with Multiple Fact Tables 

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.