DAX, Power BI

Mixing Table Functions: SUMMARIZE() & FILTER()

mixing table functions in DAX power bi

Using DAX Summarise and Filter in Power BI

Introduction

In our quest to understand and harness the power of Data Analysis Expressions (DAX), we continue to explore how different table functions can be combined to derive insightful results.

Last week, we touched on the interplay between the ALL() and FILTER() functions.

This week, we delve into the SUMMARIZE() and FILTER() functions, unpacking their individual utilities and synergistic power when used in combination. 

If you’re looking for a comprehensive, step-by-step guide to Power BI, don’t forget to download our free crash course eBook at the end of this post.

Understanding DAX in Power BI

DAX, a formula language developed by Microsoft, equips us with the tools for tabular transformations and data enrichment. Among the categories of DAX functions are the table functions, such as ALL(), SUMMARIZE(), FILTER(), which as the name suggests, generate tables as outputs of their computations. Each table function offers a standalone utility, but when used together, they can unravel significant insights from raw data.

Deep Dive: The SUMMARIZE Function

The SUMMARIZE() function in DAX allows us to create a summary table based on specified criteria in the function’s arguments. The arguments determine the group-by principles for the summary table.

For a more detailed guide on SUMMARIZE(), refer here.

Deep Dive: The FILTER Function

The FILTER() function in DAX filters data according to the conditions you set. It returns only the subset of data that meets the criteria.

For a comprehensive step-by-step guide on the FILTER() function, refer here.

 

Combining SUMMARIZE and FILTER functions in DAX

We will now demonstrate how to use FILTER() and SUMMARIZE() together to create a summary table featuring products from the “Clothing” category with sales exceeding $250,000. Follow the steps as directed:

1. Click on the Modeling tab in the Ribbon.

2. Click on New Table.

Using summarise and filter function in dax power bi

3. Enter the following expression in the formula bar and press Enter:

summarise and filter function in dax

In this expression:

  • The product table is grouped by the product category, subcategory and sales amount in a virtual table.
  • From this table, only the products from the clothing category that have total sales greater than $250,000 are filtered.

4. Click on Sales column in the Mixing table functions – FILTER AND SUMMARIZE (CT) in the Fields pan.

Sales Column in the table calculated by using SUMMARIZE() and FILTER() in power bi

Column tools box opens. 

colum tools menu in power bi dashboard

5. Click on the $ symbol and write 2 in the text box to set formatting.

6. Click on the Data tab in the left Ribbon.

Data Tab in Power BI Desktop

7. Click on Mixing table functions – FILTER AND SUMMARIZE (CT) in the Fields pan 

Results of DAX Query

Notice that a summary table of products from clothing that contain sales greater than $250,000 has been created. 

Best Practices to use Summarise and Filter functions in DAX

It’s important to understand the unique standalone functionality of each table function. However, combining them can lead to more refined insights from your data.

Always remember to format your data for better readability.

Conclusion

In this blog, we’ve used SUMMARIZE() and FILTER() together to create a summary table based on a particular set of criteria. By understanding and leveraging DAX table functions, we can transform raw data into meaningful insights, thereby enhancing our Power BI analyses. To dig deeper into Power BI and become a pro at it, make sure to download our comprehensive Power BI Crash Course eBook.

Power BI Crash Course eBook

Ready to take your Power BI skills to the next level? We’ve put together a detailed Power BI Crash Course eBook just for you! This downloadable guide is designed to help you understand Power BI from the basics to advanced topics, with practical examples and step-by-step instructions.

P.S. This guide is free for a limited time, so get your copy now!

In-person Power BI Training

We offer comprehensive Power BI training across Australia, tailored to suit different learning styles and proficiency levels. We deliver all our trainings in collaboration with Dynamic Web Training. 

Back to list

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *