I n the past two chapters ( chapter 5 - chapter 6 ), Query Editor was the Power BI tool that completely occupied our attention, showing us how to build the tables of a model from the functionalities of extraction, transformation, and load (ETL) that it has.

Once the creation of the tables is finished, in this section we will continue our task of developing the data model, using on this occasion the Power BI designers most directly related to the Power Pivot engine: Data and Relationships, responsible for the development of measures, hierarchies and relationships between the tables of the model. We will also pass briefly through the Designer Report, just to check the values of the measurements generated in the data table.

Finished the work of preparing tables from the Query Editor we will place in the Data designer, which among other operations, we will use to create the measures of the model through expressions written in DAX, the query language of the Power Pivot engine integrated in Power BI, as well as to format the values of the columns and create hierarchies in the tables.

The following figure shows an overview of this designer, with a central area dedicated to the display of the selected table in the Fields pane on the right, and the main work options in the Modeling menu tab.

As we have already indicated, from this designer we will create the main measure of the model, to which we will give the name of Total Population (to simplify, we will avoid the use of tilde and other special characters in the names of the measures), and whose objective will be to make a count of the individuals that make up the Register of inhabitants that we are analyzing.

To do this, we will select the PopulationCensus table and click on the New Measure menu option, from the Calculations group, Modeling tab, which will position us in the formula bar of the table, where we will write the following DAX expression.

Total Population = COUNTROWS (PopulationCensus)

As we have already guessed, in the previous expression, the COUNTROWS function is responsible for counting the rows of the PopulationCensus table that we pass as a parameter, to obtain the total number of inhabitants.

Then we will format the measure by adding a thousand separator characters, which we will select in the Modeling menu tab, Formatting group, Thousands separator option.

To check the operation of the measure that we have just created, we will move to the Designer Report, whose purpose is to build a graphic representation of the information in the data model, through the visual objects contained in its palette of controls.

As a general rule, the steps used to add a control with a minimum of functionality to the Report designer are basically the following:

1)     Select the control in the palette of the Visualizations panel.

2)     The previous action places a copy of the control in the design area.

3)     Select from the fields of the Fields panel the measure (s) that we want to represent graphically, and place it in the properties of the Fields section of the control.

4)     Depending on the power and visual richness of the control, use some of the properties of the Format section to improve its appearance.

In this first approach to the Power BI report designer, we are not going to create a very complex or elaborate presentation, and we just need to verify that the data table count is correct through visual control.

For the demonstration, we will use the Card control, whose operation consists of a simple visualization of the value of a measurement.

Once the control is selected in the Visualizations panel, we will click on the PopulationCensus table of the Fields panel to display its elements (fields, measurements, hierarchies) and check the box of the Total Population measure, which will automatically go to the Fields property of the control, belonging to the section of the same name, within the Visualizations panel. As a result, the control will display the measurement value using an abbreviated numerical format.

To show the value of the measurement with the complete number format, we will click on the Format section of the Visualizations pane, and within the Data label section, we will change the value of the Display units property from Auto to None.

To ensure that the number of individuals is correct, we will return to the Query Editor by clicking on the Edit Queries menu option, External data group, Home tab. Once there, in the PopulationCensus table, we will select the Count Rows menu option, from the Table group, Transform tab, which will also count the records in the table, returning the same value (without formatting) as the measurement used in the Report designer.

Once this verification is finished, we will delete the counting operation from the Applied Steps list in the Query Settings panel, and close the Query Editor window, applying the changes to return to the report designer.

The fact that the previous rapid test of verification of the value of the measure has given the expected value does not mean, at all, that the model preparation work has been completed. As evidence of this, if we add a control to the designer of the Report panel, in which in addition to the Total Population measure, fields from any other table of the model intervene, the results will not be as desired.

As an example, we are going to add a new page to the pbix file we are working with, by clicking on the New Page button at the bottom of the Power BI window. On this page, we will add a Stacked column chart control, to measure the number of inhabitants per age group, showing the representation of each section in a column. Once the control is placed in the design zone, we will select the Total Population measure, which will be assigned to the Value property of the Fields section. In the Age table, we will choose the Decennial Age field, which will be assigned to the Axis property; As a result, the control should show the columns corresponding to each age range with different sizes, thus reflecting the number of existing people by range.

However, this is not the behavior obtained, since the control shows all the columns with the same size, without correctly reflecting the population for each age group, which is a symptom of some type of lack in the model configuration sections.

The origin of this problem lies in the lack of relationships between the data table and the model search since without a relationship between the Population and Age tables, the Power Pivot engine does not know the amount of population that should be assigned to each age group, and therefore, the control is not drawn correctly.

To solve the problem posed in the previous section, we will locate ourselves in the Relationships designer, who, as indicated by his name, has the purpose of establishing the relationships between both types of table of the model: data and search, so that the visual representation of the data from a Power BI report, correctly reflect the information that you want to transmit to the user.

Within the work area of ​​this designer, we see an outline of the tables that make up the data model. If, due to their quantity, they were not all insight, we can adjust their size by clicking on the Fit to screen button or the Zoom controls, located at the bottom.

We can also drag the tables to place them more properly, according to the relationships we need to create.

Next, we will proceed to create the relationship between PopulationCensus and Age, for which we will select the column AgeID in the PopulationCensus table, and drag it to the column of the same name in the Age table, establishing the relationship with a cardinality of many to one.

If we double click on the line that represents the relationship, the Edit relationship window will open, through which we can see its characteristics.

Upon returning to the Report Designer, the Stacked column chart control will now reflect the information properly, although the order of the age ranges will not be totally correct, which we will solve in the next installment dedicated to the development of reports with the Designer Report.

The list will show the rest of the relationships that we must establish between the PopulationCensus table and the other tables, so that the controls that we incorporate into our reports, dashboards, etc., adequately show the calculations of the model metrics.

Alternatively, we can also manage the model relationships from the Manage Relationships window, accessible from the menu option of the same name, located on the Home tab, Relationships group.

A hierarchy is an element, which, once defined in a table of the model, will allow us to analyze its information through levels, which represent various categories of related data within that table. As an example, we will create a hierarchy based on the MunicipalityCountryBirth table, which will consist of three levels: country, province, and municipality.

In the first place, we will place ourselves in the Data designer, and in the Fields panel, we will display the list of fields of the MunicipalityCountryBirth table. Then we will right-click on the Country Birth field, choosing the New hierarchy option.

This action will create a hierarchy with the default name Country Hierarchy. As a next step, we will successively drag the ProvinceBirth and MunicipalityBirth fields until they are released within the name of the hierarchy, now being composed of the three levels mentioned above.

Finally, we will change the name of both the hierarchy (CountryProvinceMunicipality) and its levels (Country, Province, and Municipality), by right-clicking on each element and choosing the Rename option.

In addition to the measurements performed by the count of individuals, the demographic analysis employs a series of additional indicators, whose purpose is to reflect the distribution of the population through the variables of sex and age; they are called Demographic Structure Indicators.

Starting from three large age groups, with intervals 0-14, 15-64 and 65+ (65 years and over), the structure indicators measure aspects such as the proportion of a certain group over the total population; the relationship of the groups of elderly or young people with respect to adults; and the reason between old and young.

The demographic change processes experienced in certain populations (low fertility, high life expectancy, etc.) have caused that, in addition to the traditional grouping indicated in the previous paragraph, some organizations such as Eurostat use a second grouping based on intervals 0- 19, 20-59 and 60+ (60 and older). The following figure shows these two types of age groups.

 

We will begin by creating the three measures that represent the number of inhabitants for each age group (young, adults and older). As we did previously with the Total Population measurement, we will return to the PopulationCensus table of the Fields pane, and select the New Measure menu option, to create the measures Young Population, Adult Population and Major Population, whose source code we see below.

Youth Population = CALCULATE (

[Total population],

Age [AgeID]> = 0 && Age [AgeID] <= 14

)

 

Adult Population = CALCULATE (

[Total population],

Age [AgeID]> = 15 && Age [AgeID] <= 64

)

 

Major Population = CALCULATE (

[Total population],

Age [AgeID]> = 65

)

The CALCULATE function, used to create these measures, evaluates the last expression as the first parameter (Total Population measurement of the PopulationCensus table) by modifying its original evaluation context by using filter expressions (selection of intervals on the AgeID column of the Age table with DAX operators ) located in the second and subsequent parameters.

Following the recommendation of the reference sheet available in PowerPivotPro, when writing the code of a measure within which we have to refer to another previously created measure, for that existing measure, we will only use its name without first putting the name of the table.

To check the values that we have just created, we will return to the Report Designer, and on a new page, we will add a Multi-row card control, which will facilitate the simultaneous visualization of several measures by including them in their Fields property.

Demographic Structure Indicators. Proportions over the Total Population

This type of indicator calculates the percentage that represents each age group (youth, adults, and the elderly) with respect to the total population, so we will have to create three measures, one for each group. To obtain it, we will use the DIVIDE function, as we see in the following block of code, where the measure of the corresponding population group acts as the numerator and the total population as the denominator.

Young Proportion = DIVIDE ([Young Population], [Total Population])

ProportionAdultos = DIVIDE ([Adult Population], [Total Population])

Major Proportion = DIVIDE ([Major Population], [Total Population])

Additionally, we will format each of these measures as a percentage with two decimals.

And we will also use a Multi-row card control to visualize its values.

Demographic structure indicators. Dependency Indices

These are indicators that establish a relationship between the groups of young and / or older population (numerator) with respect to the adult group (denominator), so we will use the following expressions to calculate them.

Youth Dependency Index = DIVIDE ([Youth Population], [Adult Population])

Major Dependency Index = DIVIDE ([Major Population], [Adult Population])

Demographic Structure Indicators. Aging Rates

And we finish the calculation of indicators with this measure, which allows us to observe the aging process of the population through the ratio between the population of elderly and young people using the following formula.

Aging Index =
DIVIDE ([Older Population], [Young People])

And with the creation of demographic indicators, we finalize this chapter, in which we have shown the role that Data and Relationships designers play in the process of developing an information system with Power BI, specifically in the section on creating the model of data, through the elaboration of the metrics, relationships, and hierarchies.