Most of the modelling work is done in the Model View of Power BI. The tables are arranged in accordance with the model’s Filter Flow, where filter context flows downstream.

Data modelling is not just about establishing connections between individual data tables, but to also define how the data will appear in the report view (i.e. front-end).

<aside> <img src="/icons/report_orange.svg" alt="/icons/report_orange.svg" width="40px" />

Note:

If a table connection is established on a commonly identified column, but the data is still not appropriately grouped by a set dimension in the report view, check to see if the data type is the same for the common column in both tables.

</aside>

The modelling steps are mentioned as follows:

1. Marked PK for each lookup table, using table properties.
2. Connected Calendar, Product, Territory and Customer Lookup tables to Sales Table.
3. Tested all connections via grouping on a dimension in the report view.
4. Established an ‘inactive’ relationship between calendar date and StockDate.
5. Connected Calendar, Product & Territory lookup tables to Returns Table.

   1. Sales Data and Returns Data can’t be directly connected (no PK/FK relationship)

   2. Connection can be established via related lookups, allowing filtering using fields from any shared lookup tables

   3. Filtering/Grouping via Customer Lookup won’t be possible since it isn’t shared, and Filter Flow between the two facts tables can’t be established.

      <aside> <img src="/icons/report_orange.svg" alt="/icons/report_orange.svg" width="40px" />

      Note:

      Bi-directional filters can sometimes override the problem of improper filter flow. However, they should be avoided because they can lead to errors that can be difficult to track! They can also lead to ambiguity → multiple/conflicting paths of filter flow.

      </aside>

   4. Hid FK in report view from facts tables, to prevent filter flow related errors for the front-end users

6. Generated multiple model layouts to represent each portion of the overall model more clearly.
7. Changed date format to short format in the Table view for all tables (different from ETL, determines how the data is displayed in the report’s front-end).
8. Changing currency data format to round to 2 decimal places (cleaner values in the front-end).
9. Assigned Data Category to “geospatial” for geographic columns in Territory Lookup, to make it easier to embed maps in report view.
10. Generated Geographic Hierarchy in the Territory Lookup: Continent → Country → Region
11. Generated Date Hierarchy in the Calendar Lookup: Start of Year → Start of Quarter → Start of Month → Start of Week → Date