Friday, 17 August 2018

How to use ALL and ALLEXCEPT Functions in Power BI DAX

ALL () Function in Power BI DAX
The ALL() Function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
Syntax :
ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )

--<table> : The table that you want to clear filters on.
--<column> : The column that you want to clear filters on.
The argument to the ALL function must be either a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the ALL function.
ALL(Table) :
Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied.
This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value.
ALL (Column[, Column[, …]]) :
Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table.
The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.
Example-I :
Suppose we have the Sales data by Region as follows..
Now, we apply a Filter in Region, excluded "Africa" from the Region_Name column.



Now, If you wants to ignore that Filter and wants to calculate Sum for All regions, we can calculate using ALL () with SUM() Functions as follows..
All_Region_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),
                       ALL(DimRegion[Region_Name])
                      )
Here, the ALL() Function clears/ignore the filters on column [Region_Name]. 
Result :
Example-II :
Now we will calculate the Sales Penetration by Country using the SUMX ( ) , CALCULATE() and ALL() Functions as follows..

Sales_Penetration_by_Country = 
                                                SUMX(FactSales,FactSales[Net_Sales])/
                                                CALCULATE(SUM(FactSales[Net_Sales]),ALL(DimRegion))

Result :

ALLEXCEPT () Function in Power BI DAX
Removes all context filters in the table except filters that have been applied to the specified columns.

Syntax :
ALLEXCEPT( {<table> | <column>[, <column>[, <column>[,…]]]} )
--<table> : The table that you want to clear filters on.
--<column> : The column that you want to clear filters on.
The first argument to the ALLEXCEPT function must be a reference to a base table; all subsequent arguments must be references to base columns. You cannot use table expressions or column expressions with the ALLEXCEPT function.
ALLEXCEPT(Table, Column1 [,Column2]...):
Removes all context filters in the table except filters that are applied to the specified columns.
This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

Example :
We will calculate the SUM by ignoring the Filters on other Columns of the DimRegion table except on DimRegion[Region_Name] column, using the ALLEXCEPT Function as follows..
AllExcept_Region_Sales = 
                                    CALCULATE(                      
                                                      SUM(FactSales[Net_Sales]),
                                                      ALLEXCEPT(DimRegion,DimRegion[Region_Name])
                                                      )                     
Result :


------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts