Power Bi Training Courses London UK

Enrol Now
Get In Touch

Category: DAX FUNCTIONS

Testing table functions in measures

DAX is a formula language used for creating calculated columns, measures, and tables. It is a common misconception that the table functions in DAX are used to create calculated tables only. However, this is not true. In this blog, you will learn how to use table functions in measures.  To test the table functions in […]

Using Variables in DAX

Variables are used in DAX to optimize the DAX code. The DAX engine evaluates the variables only once, making the code faster and more readable. Using variables in DAX helps you: Improve code readability. Improves code performance. Reduces complexity. Simplifies code. Let’s look at the structure of the code. VAR key word is used to define […]

Increasing DAX Code Readability

DAX is a formula language developed by Microsoft to help data analysts enrich their data set and extract useful information out of raw data. DAX code snippets have nested function calls which are difficult to understand if you’re a beginner in DAX. Therefore, variables are used to break the code into smaller understandable units. In […]

Mixing Table Functions: SUMMARIZE() & FILTER()

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. […]

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.

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.

Download Beginner’s Guide to Power BI

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.

Power BI Essentials course in Australia
Power BI Report Design course in Australia
Power BI Advanced course in Australia
Power BI DAX course in Australia
Previous
Next
Make an Enquiry about Power BI Courses

Contact Us

Kindly submit your details and a training consultant will contact you shortly.
  • 02080049141