1. Supporting tables
1.1. These are little different to intermediary tables because they are static data tables with no relationships to other tables in the data model but exist to enable dynamic measures
1.1.1. An example of a dynamic measure could be Sales by Frequency Group, where "Frequency Group" is a categorisation (e.g. High, Medium, Low) that does not exist in the data model
1.2. Example: Purchase Frequency support table for "{measure} by Frequency Group" dynamic measures
1.2.1. 1. Create Purchase Frequency table
1.2.1.1. Via Report Editor and Report view, click "Enter data" and create this simple support table
1.2.1.1.1. Before coming up with appropriate Min and Max values for each category, you would do some simple count-based analysis of the sales transaction table per customer to see the range
1.2.1.1.2. Support tables remain unrelated to other tables in the data model, and by convention we can place them to the side of the model (measure groups being at the bottom) so they are clearly identifiable
1.2.2. 2. Create a Dynamic Measures table
1.2.2.1. As with all measure group tables, we start by creating an empty table using the "Enter data" button
1.2.2.1.1. Add new measure to Dynamic Measures
1.2.3. 3. Test and use the new dynamic measure with the support table
1.2.3.1. Here's a table of customer names, frequency groups and total sales (by customer by frequency group)
1.2.3.2. Here's a donut chart visual of frequency group and total sales (by frequency group)
2. What If Parameters
2.1. These get added to the model using the Modeling | New Parameters button and are a special type of supporting table (see supporting tables node above)
2.2. Example: Add what if parameter slicer for sales forecasting
2.2.1. 1. In a visualisation (e.g. a bar chart) that shows sales by something (e.g. by country), add one or more constant lines (to represent targets)
2.2.2. 2. Add what if parameter via Modeling | New parameter
2.2.2.1. In this example, we name the parameter "Sales Growth Percentage" and set the range to be from 0.02 to 1 (which we'll use to represent growth between 2% and 100%) and increments of 0.01 (1%)
2.2.3. 3. Format values the number series generated by the added parameter
2.2.3.1. In this case we format as percentages
2.2.3.2. Note how the added query includes a single column with values created via the DAX function GENERATESERIES and a measure that uses SELECTEDVALUE
2.2.3.3. Also note in the model view that we should place the new table on the right because it is a supporting table (no relationships with the core model)
2.2.4. 4. Add a new measure that uses the new auto-generated measure (i.e. uses the what-if parameter selected by the slicer)
2.2.4.1. Total Sales Forecast = [Total Sales] * [Sales Growth Percentage Value]
2.2.5. 5. Drag new measure onto visualisation and use what if parameter slicer to visualise effects of different uplifts to total sales (or whatever measure you choose), which can help with setting sales targets for the future
2.2.5.1. Here we see effect on total sales if we were to achieve a target of 20% growth in all countries
3. Enhancing lookup tables
3.1. Example: add a categorising group to a Country lookup table
3.1.1. We can leverage the relationship from Countries to Regions to Sales in order to identify a categorisation for countries that differentiates between "strong sales" and "weak sales"
3.1.1.1. 1. Add helper column named All Sales to get the total sales aggregated to each country
3.1.1.1.1. Note that [Total Sales] is a measure, which means there is an automatic context transition from row context to filter context
3.1.1.2. 2. Add enhanced column named Country Group and use a SWITCH function to define values based on the helper column
3.1.1.2.1. In the case we want any country where total sales exceeds $10M to be categorised as "Strong Sales" and any country at or below $10M total sales to be categorised as "Weak Sales". We add BLANK() as a catch-all, even though we don't expect any country sales to hit this final clause of the SWITCH.
3.1.1.3. 3. Hide the helper column
3.1.1.3.1. The All Sales column is only needed in order to facilitate the Country Group calculated column. We hide it so it does not display in the report view.
3.1.1.4. 4. Use enhanced column in a visualisation
4. Organising the data model
4.1. Once finished in Query Editor, you click Close & Apply to return to the main Power BI report editor, and click on the Model view
4.1.1. One system for setting up your model is to place all your fact tables at the bottom and dimension (lookup) tables at the top
4.1.1.1. When organising the model like this, it can be helpful to think of the dimensions/lookups flowing down into the fact tables below
4.1.1.1.1. To continue the "waterfall" analogy, we can think of filters being applied at the top level and flowing down to the second level, reducing metrics (e.g. sum, count, etc) in the lower level by way of the relationships defined in the model
5. Setting up complex models
5.1. Example is adding a budget table, 2016 regional sales budgets based on 2015 sales
5.1.1. In the Power BI report editor, Data view, we have option to add a new table via the Table tools menu
5.1.1.1. Add DAX formula to create a 2-column table (Country and 2015 Results)
5.1.1.1.1. You can use Shift+Enter to get line breaks in the expression and Tab to indent
5.1.1.1.2. A new table is created
5.1.1.2. Once we create a new table in this way (i.e. using DAX), we can then add a new column (2016 Budgets) via the Column tools menu
5.1.1.2.1. Hide the 2015 Results column from the report view
5.1.1.3. When we try to create the relationships for the new table in the model view, we see a problem
5.1.1.3.1. The new 2016 Regional Budgets table is at the grain of country and year
5.1.1.3.2. When we try to relate the 2016 Regional Budget fact table to the Regions lookup table, we see that the direction of the relationship flows upward
5.1.1.4. To resolve this relationship problem in our model, we need to add another table for Countries
5.1.1.4.1. In our data model view, we organise the new Countries lookup table at the very top level and relate it to Regions below
6. Handling absent lookup tables - alternative approach
6.1. Attributes that you want to filter and slice on generally belong in lookup or dimension tables
6.1.1. It is a common scenario to find such attributes lingering in your fact tables, and a design goal is to break these out into new lookup tables for your model
6.2. Example 1: break out warehouse code from sales fact into a table that acts both as a lookup and as a fact summary (by warehouse)
6.2.1. Key point here is that there are many lines with repeating warehouse codes in the sales fact and the new table will have relatively few rows, each representing a distinct warehouse code
6.2.2. 1. In report editor, go to data view, then Table Tools menu, click New Table and create a single column that summarizes 'Sales'[Warehouse code]
6.2.2.1. We use the following DAX expression to create the list of distinct warehouse codes:
6.2.2.1.1. Warehouses = SUMMARIZE('Sales',Sales[Warehouse Code])
6.2.3. 2. Go to models view and create the relationship between new Warehouses lookup table and Sales fact
6.2.4. 3. Return to data view and Table Tools, select the new Warehouses lookup table, click New Column then create new "Warehouse Sales" column that sums all 'Sales'[Line Total] values by warehouse code
6.2.4.1. We use the following DAX expression to create the new column:
6.2.4.1.1. Warehouse Sales = CALCULATE(SUM('Sales'[Line Total]))
6.2.4.2. We can use the Column tools menu to apply formatting (e.g. currency format) to the new column
6.3. Example 2: break out multiple channel attributes from sales fact into a lookup table
6.3.1. 1. In report editor, go to data view, then Table Tools menu, click New Table and create a 4-column table from a summarization of those columns in the 'Sales' table
6.3.1.1. We use the following DAX expression to create the list of distinct channel attributes:
6.3.1.1.1. Channel = SUMMARIZECOLUMNS('Sales'[Channel], 'Sales'[Channel Code], 'Sales'[Channel Importance], 'Sales'[Channel Alt. Name])
6.3.2. 2. Go to models view and create the relationship between new Channel lookup table and Sales fact
7. Constructing measure tables/groups
7.1. Another best practice is to create all your measures inside one or more dedicated measure group tables
7.1.1. This simply helps make all your measures much easier to locate and use when you build the report
7.2. Example: create a new "Total Sales" measure from the 'Sales'[Line Total] column and add to new group named "Key Measures"
7.2.1. 1. Create new measure initially in table where the main fact metric is you want to base your measure on
7.2.1.1. DAX is:
7.2.1.1.1. Total Sales = SUM('Sales'[Line Total])
7.2.2. 2. Create a new measure group via the following trick:
7.2.2.1. Create a new table via report editor Home | Enter Data
7.2.2.1.1. Name the new table "Key Measures" and leave the rows for Column1 blank, then click Load
7.2.2.2. Move the "Total Sales" measure to the new "Key Measures" table
7.2.2.3. Delete the redundant Column1 from the "Key Measures" table
7.2.2.3.1. Note that the "Key Measures" group automatically jumps to the top in Data explorer
7.2.3. 3. Layout the new measure groups at the bottom of the model (in model view)
7.2.3.1. Note that measure group tables do not get related to regular model tables via relationships because they are based on regular table attributes (so the relationships are implicit)
7.2.3.2. The idea is no to let these special tables rest in their default position in the model, but to line them up at the bottom as a way of keeping them organised and visually obvious in their identity as measure groups
8. Leveraging intermediary tables
8.1. Intermediary tables are any tables in your model that do not belong to the core dimension/lookup layer and the core fact layer
8.2. Example: create a budgets table by product by month, based on a 5.5% uplift of total sales
8.2.1. 1. In report editor, go to data view, then Table Tools menu, click New Table and create a 3-column table based on a calculation of Total Sales plus 5.5% per product name and month.
8.2.2. 2. Integrate the new summary fact table into the model
8.2.2.1. The first issue to deal with is that the new table is at a different grain to sales
8.2.2.1.1. When we look at our model, we cannot relate the new table to the Date table based on Month Name because the cardinality of that relationship is Many-to-Many
8.2.2.2. We add a new Month Names table using the VALUES DAX function
8.2.2.3. Then we resolve the Many-to-Many issue by relating the new intermediary table to both the Dates table and the new 2016 Product Budgets table
8.2.2.3.1. This means that any filtering on Month Name in the report should work, as it flows down through Date into Sales, and also flows down into the new budgets table