DAX functions are thriving nowadays, they have become one of the most popular and learned languages in the Business Intelligence Domain. It contains expressions that are a kind of magical in themselves and help in analysis for finding useful insights. This blog is all about providing an overview of most commonly used expressions in Power BI which are ALL, ALL SELECTED and ALL EXCEPT.

ALL – Returns all the rows in a table or all the values in a column, ignoring any filters which might have been applied.

Syntax- ALL ([<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, …]]]

This syntax will help us to get a total of sales value while ignoring any filters which might have been applied on the product name. In the image shown below we have a measure named as “ALL” present in the table which is providing a total of sales value without getting affecting through the product name column present in the table, as we have used the “ALL” function for that particular column. The output is shown below-

ALLSELECTED- Returns all the rows in a table or all the values in a column, ignoring any filters which might have been applied on the columns or the rows but keeping all other explicit filters.

Syntax- ALLSELECTED([<tableName> | <columnName> [, <columnName> [, <columnName> [,]]]])

This syntax will help us track down the sales value considering the explicit filters applied but ignoring any filters applied on rows or columns. And with the image below it can be evident that the “ALLSELECTED” measure is taking into account the explicit filter applied and providing the output.

A) Table view is representing that the “ALLSELECTED” measure sales total is not affected by the product name column present in table

B) This Table View is representing that only “Product Name” explicit filter mentioned in the ALLSELECTED measure will affect its total.

ALLEXCEPT- Returns all the rows in a table or all the values in a column, ignoring all context filters applied but taking into account the specified columns filter.

Syntax- ALLEXCEPT(<table>, <column> [, <column> [,]]) 

This particular syntax will help in calculating sales value while considering the explicit filters applied for the specified column that is “Product name” but other than that no explicit filter will work for this. Result can be seen below-

A)  Table view represents that the “Product Name” explicit filter cannot affect the ALLEXCEPT total sales.

B) Table view represents that except the “Category” explicit filter no other will have an effect on ALLEXCEPT total

Best Regards
Anmol malviya

Data Analyst | Addend Analytics

Data Labels are one of the critical components of any Power BI visual. It allows users to understand the visuals easily. With the help of Custom Data Labels, users can see a particular data point and trends.

Does your application have a REST API with token-based pagination, and you're unsure how to make it work in Power Query/M Language? Dive into this article to learn how to unlock insights effortlessly!

Discount certification voucher offer for Fabric Data Engineer (Exam DP-700).

Live learning sessions starting Jan 28th.

The ability to use SVG specifications in Power BI opens up endless possibilities for creating stunning visuals. However, there are a few challenges you might face.

Microsoft has introduced an exciting new feature in Power BI: Data Limit. This update empowers users to manage large datasets effectively, delivering smoother performance and ensuring insights remain accurate. Let’s explore how this feature works and why it’s a game-changer for your data analysis needs.

Learn the difference and what it takes to transition to Fabric successfully!

In this article, we’ll explore the power of X-Functions like SUMX, AVERAGEX, and more, and how they can transform your data modeling in Power BI. Let’s clarify what we mean and dive into practical examples to enhance your analytics workflows.

Downloadble Resources 

How the Text Slicer Works
The text slicer enables the user to input his specific text. He then specifies which particular data field would be filtered. As soon as one types the text in the slicer's input box, the information will automatically reduce to showing only those data where the text has been located. It is beneficial when dealing with huge lists because speed and accuracy must be delivered quickly on the filter.

Managing stock levels dynamically is essential for effective inventory management. A crucial aspect of this task involves calculating cumulative quantities to track the balance of products over time. In this example, we have Sales and Purchasing tables, and we aim to create a Matrix visual in Power BI to display the purchased and sold quantities accurately. The ultimate goal is to dynamically track the stock balance (leftover products) at any given point by calculating the difference between cumulative purchases and cumulative sales.

You can download the files related to this article using the following link:
Materials 

In this newsletter, we’ll dive into hierarchical directories and how they’re used in analytics. Let's clarify what we mean.

Downloadable Materials 

Power BI can connect to, transform and visualise data. Which is great but how do we get that modified data out?

One way is using a Power Automate flow to connect to the published Power BI dataset.

In this video we discuss how to manage a notebook PySpark code throughout the deployment pipeline stages, so that it adjusts dynamically to each deployment stage without needing manual interaction.

All About Power BI Metrics, A hidden Gem