Creating a Physical Table with DAX

In DAX, you can use the DATATABLE or SELECTCOLUMNS in conjunction with FILTER and SUMMARIZE to create a new physical table. Below is an example of how to create a new table that consolidates data with complex filtering logic.

Requirements

Let’s say you want to create a new table named SalesSummary which:

• Aggregates total sales by product category.
• Filters to include only categories with sales greater than $10,000.
• Further filters for records from the year 2022.

DAX Formula

Here is a DAX formula that satisfies the above conditions:

SalesSummary = 
    VAR FilteredSales = 
        FILTER(
            Sales,
            Sales[TotalAmount] > 10000 && 
            YEAR(Sales[OrderDate]) = 2022
        )
    RETURN
    SUMMARIZE(
        FilteredSales,
        Products[Category],
        "Total Sales", SUM(Sales[TotalAmount])
    )

Formula Explanation

Components Breakdown

1. Variable Definition (VAR)

   • FilteredSales is defined to hold a filtered version of the Sales table.
   • The FILTER function is used to restrict the dataset to transactions where TotalAmount is greater than $10,000 and the OrderDate falls within the year 2022.

2. Summarization

   • SUMMARIZE is employed to create a new table by aggregating filtered data.
   • The first argument is the filtered dataset (FilteredSales), and the second defines the grouping column, which is Products[Category].
   • The third part computes the total sales using the SUM function on Sales[TotalAmount], which results in a calculated column named "Total Sales".

Logic Flow

1. Start with a base table (Sales).
2. Apply a filter to get relevant rows based on the defined criteria.
3. Summarize the results to produce a new physical table with aggregated data.

Practical Example

Context

Imagine you are a data analyst in a retail company, and you want to analyze the product performance in 2022. You want to understand which product categories surpassed sales of $10,000.

Usage

• Before Execution: You have a dataset in the Sales table with fields like TotalAmount and OrderDate, along with a related Products table that includes Category.
• After Execution: The new table SalesSummary will show each product category and the corresponding total sales, limited to those qualifying the set conditions.

Resulting Table Structure

The resulting SalesSummary table will have the following structure:

Conclusion

This DAX formula creates a complex new physical table based on specific filtering criteria and aggregations. By understanding how to utilize FILTER, SUMMARIZE, and other DAX functions, you can manipulate and derive meaningful insights from your data effectively.

For further mastery of such concepts, consider exploring courses available on the Enterprise DNA Platform, which provide in-depth training on DAX and data modeling techniques.