ALL Function DAX

ALL is a Power BI filter function in DAX which returns all the rows in a table or all the values in a column, ignoring any filters that might have been applied.





It removes the corresponding filters from the filter context.

It does not support column expression or table expression.

It returns a table or column with filters removed.

DAX Syntax

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

table  is an existing table that you want to clear filters on.

column is table’s column that you want to clear filters on.

Lets look at an example, Here we have sample table which contains the product and their sold quantity details as you can see below.

As you can see a table visual showing a total quantity by product (productId). You can also see the grand total of quantity that is 30.

In case, if you want to display total quantity regardless product than you can use ALL function.

Following DAX named ALL_Quantity uses a ALL function which ignores the specified context filter and returns the total quantity sum, ignoring the filter on Product.

ALL_Quantity = CALCULATE (
 SUM ( TransactionHistory[Quantity] ),
 ALL ( TransactionHistory[ProductId])
)

Once you drag a ALL_Qunatity measure into table visual, it displays total quantity of all products against each product rather than displaying an individual product quantity.

If you specified any filter on product, It will ignore the filter context and returns over all total of each product as you have taken a productid column in ALL dax function.

Lets filter the product by selecting a Productid 725, 729 and 769 from slicer.

As you can see, it does filter the records in table and you can see the total quantity is now 18 for those selected productid but ALL_Quantity measure still displays over all total quantity sum that is 30 against each productid as ALL function ignoring the filter on productid column.




Also read

CALCULATETABLE

SELECTEDVALUE

FILTER

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 361 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.