Diving into DAX: Foundations and Basics

Introduction

In this first unit of the curriculum, we will be introducing the foundations and basics of Data Analysis Expressions (DAX), a formula language that is primarily used in Power BI, Analysis Services, and Power Pivot in Excel.

As a data scientist, understanding and applying DAX would empower you to get the most out of your data and to analyze it in a highly efficient manner. For our practical implementation, we will specifically focus on using the Power BI and DAX to perform sales data analysis using the TOPN function.

DAX and Power BI

DAX is a collection of functions, operators, constants, and values that can be used to define custom formulas in Power BI, Analysis Services, and Power Pivot in Excel. DAX formulas include functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Setting Up Power BI for DAX

Since Power BI is a visual analytics tool, DAX doesn't need explicit integration. On installing Power BI, both loading data into the tool and running DAX are possible out of the box.

To get started:

1. Download and install Power BI Desktop.
2. Gather your data sources.

Then, follow these steps to create your DAX formulas:

1. Open Power BI Desktop.
2. Select Get Data (Home > External Data > Get Data).
3. Select your data source and choose Load.
4. Once loaded, click on Report (left side), then open Modeling (top menu).

You can now start creating your DAX formulas by selecting the "New table" or "New column" options.

TOPN Function in DAX

The TOPN function is a table-valued DAX function that returns the top 'N' rows of a table.

The syntax of TOPN function is:

TOPN(, , ,  )
Where:

<N_Value> is the number of rows to return.
<Table> is the table which contains the rows that you want to get the top values from.
<Expression> is a real expression that's used to rank the rows. Rows will be sorted by this value in the descending order.
<Order> is an optional argument to change the sort order.

Applying TOPN Function
Here's a common use case: Suppose you have a sales data table, and you want to find out the top 10 highest-grossing products.
The DAX formula can be written as:
TopProducts = TOPN(10, 'SalesTable', 'SalesTable'[SalesAmount])
In this formula, we're requesting the top 10 rows (products) from the SalesTable based on the amount of sales (SalesAmount). The resulting table will contain the top 10 highest-grossing products, which could then be used for further analysis or presented in a report.
To apply this DAX, you just need to follow the same steps as creating a new table (Modeling > New Table), then writing this DAX in the formula field.
Conclusion
In this unit, we have introduced the foundations and basics of DAX for data analysis in Power BI. We have also demonstrated how to use the TOPN function to uncover top performers in your data sets. In the following units, we will delve deeper into more complex DAX functions and show you how to combine them into powerful formulas to gain even more insights into your data.
2navigate_next
Introduction
This segment presumes you are acquainted with the basics of DAX (Data Analysis Expressions), as well as the Power BI environment. We are primarily concerned with applying more advanced DAX functions in a Power BI context: in particular, the TOPN function, which retrieves the top 'n' records of a dataset based on a given expression.
Power BI Integration with DAX
Creating a new Measure
To manipulate data using DAX, we first need to create a new measure. In Power BI Desktop, this is done in the "Fields" pane.

Click on the ellipsis (...) next to the desired dataset.
Select "New measure".
You'll see the formula bar where you can start typing your DAX expression.

Utilizing the TOPN function
The syntax for the TOPN function is TOPN(<n_value>, <table_name>, <expression>).
Example 1:
Let's say we have a sales dataset and we want to identify the top 5 products by sales. Here's how you could do it:
Top_5_Products = 
TOPN(
5,
SUMMARIZE('Sales', 'Sales'[Product_Name], "Total Sales", SUM('Sales'[Revenue])),
[Total Sales],
DESC
)
In the formula above, we use the SUMMARIZE function to create a grouped table, which includes each product and its total revenue. Then TOPN returns only the top 5 products from this table. The DESC keyword denotes that we want products with the highest sales.
Example 2:
If we want to find the top salesperson in every region, it becomes slightly more complex. You'd need to iterate over a table of regions, then within each region, find the top salesperson:
Top_Salesperson_Each_Region = 
SUMX(
    VALUES('Sales'[Region]), 
    VAR Region_Sales = 
        CALCULATETABLE(
            'Sales', 
            ALLEXCEPT('Sales', 'Sales'[Region])
        )
    RETURN TOPN(
        1, 
        SUMMARIZE(
            Region_Sales, 
            'Sales'[SalesPerson], 
            "Regional Sales", SUM('Sales'[Revenue])
        ), 
        [Regional Sales], 
        DESC
    )
)
In this formula, the SUMX function is used to iterate through each unique region (VALUES(Sales[Region])). For each region, we calculate a Region_Sales table, which includes only sales in that region (ALLEXCEPT). The VAR keyword is used to create a variable to hold this table.
Then, for each region, we use TOPN to find the top salesperson. Again, we're using SUMMARIZE to make a table of salespeople and their total sales. Finally, we return the salesperson with the highest sales in that region. 
Conclusion
The integration of DAX into Power BI significantly empowers data analysis. As shown in the examples, the TOPN function can be applied to solve complex problems, such as identifying the best performing items, salespeople, regions, and more. Remember, while DAX can seem complex, it becomes far more accessible with practice and careful implementation in Power BI.
3navigate_next
Mastering Advanced DAX Formulas: Exploring the TOPN Function
The TOPN function is a critical component in advanced DAX formula usage. You can use it to return the top N rows of a table based on a given expression. Therefore, it's instrumental in gaining high-quality insights from large datasets.
This guide will walk you through practical examples and explain how the TOPN function can be used in an advanced sales data analysis context.
Prerequisites
Before we begin, make sure your Power BI environment has a sales dataset loaded. We need a dataset with at least the following columns: Sales (or Revenue), Products, and Year or Date.
Implementing TOPN in DAX
The syntax of the TOPN function is as follows.
TOPN (, ,  [, , [] …])
Here:

<n_value> is the number of rows to return
<table> is the input table from which to return rows
<expression> is the expression evaluated for each row of the table
<order_expression> is the expression that ranks the rows to determine their order.
<order> defines whether the ranking should be in ascending or descending order. It's optional, and the default order is descending.
Determine Top Performing Products
For instance, if you wanted to determine your top 3 best-selling products for the year 2021, you could use the TOPN function as follows:
Top3_Products_2021 =
TOPN (
    3,
    FILTER (
        SUMMARIZE (
            Sales, 
            Products[Product_Name], 
            "Total_Sales", SUM ( Sales[Revenue] )
        ), 
        YEAR ( Sales[Date] ) = 2021
    ), 
    [Total_Sales]
)
This DAX formula will create a new table Top3_Products_2021 with the top 3 products based on revenue for the year 2021. 
Compare Current Year Sales to Previous Year
If you want to compare the current year's top 10 products to the previous year's, you could use TOPN in conjunction with EXCEPT function as follows.
Top10_Products_This_Year = 
TOPN (
    10,
    SUMMARIZE (
        Sales, 
        Products[Product_Name], 
        "Total_Sales", SUM ( Sales[Revenue] )
    ),
    [Total_Sales]
)

Top10_Products_Last_Year = 
TOPN (
    10,
    CALCULATETABLE (
        SUMMARIZE (
            Sales, 
            Products[Product_Name], 
            "Total_Sales", SUM ( Sales[Revenue] )
        ), 
    SAMEPERIODLASTYEAR ( Sales[Date] )
    ),
    [Total_Sales]
)

New_Top10_Products = 
EXCEPT ( 
    [Top10_Products_This_Year],
    [Top10_Products_Last_Year]
)
The New_Top10_Products table will now have the top 10 products of this year, which weren't in the top 10 last year.
Bear in mind that DAX is a functional language. You can develop very complex formulas by combining these functions, and using one as an argument to another.
These are practical examples of how TOPN function can be used for sales analysis in a real-world scenario. But remember, the power of DAX lies in its flexibility. The TOPN function can be employed creatively to suit the specific needs of your data and your business.
Remember, this is only a practical implementation of the TOPN function, its usability is not limited to these scenarios. The real prowess of DAX and the TOPN function is much more and can be optimally utilized as per your specific needs and requirements.
4navigate_next
Practical Implementation: Tracking Top 10 Sales by Region
The task at hand is to create a solution for tracking the top 10 sales by region using Power BI and DAX. We're using the DAX function TOPN, which is perfect for this requirement. TOPN returns the top 'N' rows of a table or value. In our scenario, we want the top 10 sales, so we will use TOPN(10,...). 
Assuming that we have two tables: Sales and Regions. The Sales table contains columns like salesId, regionId, product, quantitySold, and saleValue. The Regions table contains columns like regionId and regionName. 
According to the tables and columns described, the DAX formula to create a new calculated table that displays the top 10 sales by each region can be written as follows:
Creating Calculated Table for Top 10 Sales by Region
Top10SalesByRegion = 
DATATABLE
(
"regionName", STRING,
"salesId", INTEGER,
"product", STRING,
"quantitySold", INTEGER,
"saleValue", CURRENCY,
CALCULATETABLE
(
    ADDCOLUMNS
    (
        Sales,
        "Total Sales", SUM(Sales[saleValue])
    ),
    CROSSJOIN
    (
        VALUES(Regions[regionName]),
        TOPN
        (
            10,
            ALL(Sales),
            [Total Sales],
            DESC
        )
    )
)
)
Here are the steps how this DAX formula works:

DATATABLE is a DAX function used to create a calculated table in Power BI. The output of this formula will be a new table that has columns regionName, salesId, product, quantitySold, and saleValue.

CALCULATETABLE function alters the context in which data is calculated. It changes the context or scope of the data to account for the filters applied to the sales data and the TOPN function.

ADDCOLUMNS function adds new columns to the Sales table. Here, we're adding a new column Total Sales which is the sum of the saleValue.

CROSSJOIN function returns a cartesian product of rows from two or more tables. Here, CROSSJOIN combines rows from Regions table and the resulting table from TOPN function.

VALUES function returns a one column table that contains distinct values from specified column. Here, it returns a one column table with distinct values from regionName.

TOPN function sorts data by expression and returns the largest N rows based on the value we specify (in our case, top 10 sales). Here, it sorts sales records based on Total Sales.


After creating the calculated table, you can use Power BI's visualization tools to display the result. For example, you could create a bar chart or table visual to display the top 10 sales by region. 
Please note, this implementation assumes that you have familiarity with loading data into Power Bi and creating relationships between tables in Power BI model.
More Code Generators
 Apache Flink Code Generator Apache Pig Code Generator Azure Data Factory Code Generator C/C++ Code Generator CouchDB Code Generator DAX Code Generator Excel Code Generator Firebase Code Generator Google BigQuery Code Generator Google Sheets Code Generator GraphQL Code Generator Hive Code Generator Java Code Generator JavaScript Code Generator Julia Code Generator Lua Code Generator M (Power Query) Code Generator MATLAB Code Generator MongoDB Code Generator Oracle Code Generator PostgreSQL Code Generator Power BI Code Generator Python Code Generator R Code Generator Redis Code Generator Regex Code Generator Ruby Code Generator SAS Code Generator Scala Code Generator Shell Code Generator SPSS Code Generator SQL Code Generator SQLite Code Generator Stata Code Generator Tableau Code Generator VBA Code Generator