Creating a Complete Power BI Model Using DAX and Query View: A Detailed Guide

In this article, we will walk through a detailed process of building a complete Power BI model using DAX (Data Analysis Expressions) in the Query View. The objective is to demonstrate how to create tables, apply DAX functions, join data, and create robust visuals for analyzing data.

The Power BI Query View allows users to write queries, transform data, and interact with the data model programmatically, similar to using SQL queries or writing code in Excel formulas. We’ll focus on using DAX within Query View, creating tables, defining relationships, and using measures. This approach provides flexibility in modeling complex scenarios.

1. Introduction to Query View in Power BI

The Query View is a crucial feature in Power BI where users can manipulate and create datasets using DAX queries. This capability is particularly useful when you want to build a data model using formulas rather than importing raw data from external sources like Excel or SQL databases.

Query View gives users the ability to:

• Write DAX queries for table creation.
• Create calculated tables and columns.
• Leverage the power of DAX measures to perform calculations.
• Join tables and manage relationships within the Power BI model.

Let’s dive into an example where we create tables, develop relationships, and join data for analysis.

2. Step-by-Step Creation of Tables and Relationships Using DAX in Query View

Step 1: Create a Year Table (1990-2024)

We will start by creating a table that contains the years from 1990 to 2024. This table will serve as the base to link other datasets, such as the country GDP data we’ll create later.

YearTable = 
ADDCOLUMNS(
    CALENDAR( DATE(1990, 1, 1), DATE(2024, 12, 31) ),
    "Year", YEAR([Date])
)

• CALENDAR: Creates a range of dates starting from January 1, 1990, to December 31, 2024.
• ADDCOLUMNS: Adds a custom column named “Year,” which extracts the year from each date.

After this, you will have a table with one column representing the years. This table will be key to joining the GDP data for multiple countries.

Step 2: Create a Country Code Table

Next, we need a table that contains the country names and their corresponding codes. We will define this using DATATABLE, which allows you to manually create a table in Power BI.

CountryCodeTable = 
DATATABLE(
    "Country", STRING,
    "CountryCode", STRING,
    {
        {"United States", "US"},
        {"Germany", "DE"},
        {"China", "CN"},
        {"Brazil", "BR"},
        {"India", "IN"}
    }
)

Here, we create a table with the Country and CountryCode columns. This table will be used to map the GDP data to specific countries in later steps.

Step 3: Create GDP Data for Each Country

Now that we have a year table and a country code table, we can create GDP data for each country for every year. To do this, we’ll generate individual tables for each country, containing the GDP values across the years.

DAX Code to Create GDP Data for the United States:

GDP_US_Table = 
GENERATE(
    YearTable,
    VAR GDP_US = 
        SWITCH(
            TRUE(),
            YearTable[Year] = 1990, 6000,
            YearTable[Year] = 1991, 6200,
            -- Continue adding values for every year
            YearTable[Year] = 2024, 22000,
            BLANK()
        )
    RETURN ROW("Year", YearTable[Year], "GDP_US", GDP_US)
)

In this DAX formula:

• GENERATE: Loops through each year in the YearTable.
• SWITCH: Assigns the GDP value for each year based on the country-specific data. In this case, we are adding GDP values for the United States.

We can repeat this for each country, adjusting the GDP values accordingly.

Example for Germany:

GDP_DE_Table = 
GENERATE(
    YearTable,
    VAR GDP_DE = 
        SWITCH(
            TRUE(),
            YearTable[Year] = 1990, 1500,
            YearTable[Year] = 1991, 1600,
            -- Continue for each year
            YearTable[Year] = 2024, 5000,
            BLANK()
        )
    RETURN ROW("Year", YearTable[Year], "GDP_DE", GDP_DE)
)

Example for China:

GDP_CN_Table = 
GENERATE(
    YearTable,
    VAR GDP_CN = 
        SWITCH(
            TRUE(),
            YearTable[Year] = 1990, 1000,
            YearTable[Year] = 1991, 1050,
            -- Continue for each year
            YearTable[Year] = 2024, 25000,
            BLANK()
        )
    RETURN ROW("Year", YearTable[Year], "GDP_CN", GDP_CN)
)

We now have GDP tables for multiple countries: United States, Germany, and China.

3. Joining Data Tables in Power BI Model

The next step is to combine all the individual GDP tables into a single master table using the NATURALINNERJOIN function. This function allows you to merge multiple tables on a common column—in this case, the year.

CombinedGDPTable = 
NATURALINNERJOIN(
    NATURALINNERJOIN(GDP_US_Table, GDP_DE_Table),
    NATURALINNERJOIN(GDP_CN_Table, NATURALINNERJOIN(GDP_BR_Table, GDP_IN_Table))
)

This operation creates a unified table where:

• Rows: Represent individual years (1990, 1991, 1992, etc.).
• Columns: Represent the GDP values for each country.

4. Creating Measures for Analysis

Now that we have the GDP data, we can create measures in DAX to analyze it. Measures are dynamic calculations that update automatically as you filter or slice your data.

Example Measure: Total GDP

To calculate the total GDP across all countries for any given year, you can define a measure like this:

Total_GDP = 
SUMX(
    CombinedGDPTable,
    CombinedGDPTable[GDP_US] + CombinedGDPTable[GDP_DE] + CombinedGDPTable[GDP_CN] + CombinedGDPTable[GDP_BR] + CombinedGDPTable[GDP_IN]
)

This measure uses the SUMX function to calculate the total GDP for each row (year) by summing up the GDP values of all countries.

Example Measure: Average GDP Growth

To calculate the average GDP growth rate across the years:

Average_GDP_Growth = 
AVERAGEX(
    CombinedGDPTable,
    (CombinedGDPTable[GDP_US] + CombinedGDPTable[GDP_DE] + CombinedGDPTable[GDP_CN] + CombinedGDPTable[GDP_BR] + CombinedGDPTable[GDP_IN]) / 5
)

5. Visualizing Data in Power BI

With the data model complete, you can now create various visualizations in Power BI to represent the data:

• Line Chart: To show GDP growth trends over time.
• Bar Chart: To compare GDP values across countries for a specific year.
• Table Visual: Displaying detailed GDP data for all years and countries.

Using the measures we created, you can also display key metrics like total GDP and average GDP growth.

Power BI Workflow Summary

This article detailed a complete workflow for building a Power BI model using DAX in Query View. Here’s a quick summary of the steps:

This workflow ensures a clear and structured process to manage data, create relationships, and perform powerful analysis using DAX in Power BI.