Using DAX Summarise and Filter in Power BI
Mixing table functions: Summarize() & Filter()
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.
3. Enter the following expression in the formula bar and press Enter:
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.
Column tools box opens.
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.
7. Click on Mixing table functions – FILTER AND SUMMARIZE (CT) in the Fields pan
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. Our expert trainers will guide you through real-world scenarios, helping you to grasp the nuances of Power BI and sharpen your data analysis skills.
