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
--------------------------------------------------------------------------------------------------------

Thursday, 16 August 2018

How to use FILTER Function in Power BI DAX

FILTER Function in Power BI DAX
The FILTER Function is used to reduce the number of rows in the table that you are working with, and use only specific data in calculations. FILTER is not used independently, but as a function that is embedded in other functions that require a table as an argument.

Syntax :
FILTER(<table>,<filter>) 
-- <Table> : The table to be filtered. The table can also be an expression that results in a table.
-- <Filter> : A Boolean expression that is to be evaluated for each row of the table. 
For example, [Amount] > 0 or [Region] = "India"

Example :
Suppose we have data model as follows..



The Sales by Region , Country and Customer Segment as follows.

Calculating the Sum of Sales for ASIA and EUROPE Regions using FILTER Function as follows..

Sales_Asia_Europe = 
CALCULATE(                                             
                     SUM(FactSales[Net_Sales]),
                     FILTER(DimRegion,OR([Region_Name]="Asia" ,[Region_Name]="Europe"))
                    )
Result :

Calculating Sum of Sales for ASIA and EUROPE Regions and the Countries "India" and "France" using the Logical operators with FILTER Function as follows..

Sales_India_France = 
CALCULATE(
                SUM(FactSales[Net_Sales]),
FILTER(DimRegion,([Region_Name]="Asia"||[Region_Name]="Europe")
                            && ([Country]="India" ||[Country]="France"))
                    )

Calculating Sum of Sales for ASIA Region and the Customer Segment "Electronics" using the multiple FILTER Functions as follows..

Electro_Sales_Asia = 
CALCULATE(
                      SUM(FactSales[Net_Sales]),
      FILTER(DimRegion,[Region_Name]="Asia"),
                      FILTER(DimCustomers,[Cust_Segment]="Electronics" )
                     )

Result :

Calculating Sum of Sales for specific Regions using RELATED Function with FILTER Functions as follows..here RELATED Function performs the lookup for the specified Region_Ids against the entire FactSales table and then SUM Function aggregates the corresponding values.

Lookup_Asia_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),
                       FILTER(FactSales,
                           OR(RELATED(DimRegion[Region_Id])=98769,
                                  RELATED(DimRegion[Region_Id])=98770))
                 )

Result :


If we wants to show/repeat Asia_Sales for each regions, we need use ALL Function on the FactSales Table as shown below..

Lookup_Asia_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),                                                                                                     FILTER(All(FactSales),OR(
                       RELATED(DimRegion[Region_Id])=98769,
                       RELATED(DimRegion[Region_Id])=98770))
                    )


Result :

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

Thursday, 19 April 2018

How to Select a Source Table Dynamically with a Parameter in Stored Procedure of SQL Server

How to Dynamically Select a Source Table with a Parameter and Load Data to the Final Reporting Table using a Stored Procedure in SQL Server
Scenario :
Suppose we have 4 source tables which have data for respective 4 years, descending from Current Year..CFY ( eg: 2018) , PFY1(eg: 2017), PFY2(eg: 2016), PFY3(eg: 2015).
Whenever the Source data for a specific Year is ready, we would like to load it to the Final Reporting Table by dynamically selecting the data from that respective source table based on the Parameter we Pass in to the SP.
Eg :  
Exec  [dbo].[SP_UpdateReportingData] 'PFY2'
In the above the SP will update the PFY2 (2016) data from the respective Source Table 
"[dbo].[TBL_MySample_Source_Data_PrevFY2]" Dynamically to the Final Reporting Table 
" [dbo].[TBL_MyFinal_Reporting_Data]"

=====================================================================
USE [TPREDDY_DB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--===================================================================
-- Created By            : <tpreddy123>
-- Create On             : <19thAPR2018>
-- Last Modified By : <tpreddy143>
-- Last Modified On : <19thAPR2018>
-- Description           : <Consolidate and loads the specific Year data to                                                                      Reporting Table>
--===================================================================

CREATE PROCEDURE [dbo].[SP_UpdateReportingData] (@sYear as Varchar(10))    
    
AS    

DECLARE @Update_FY AS INT  
DECLARE @Tbl_Name  AS SysName   
DECLARE @DynSQL AS NVARCHAR(MAX)   

BEGIN    
--Storing the Fiscal Year value based on Parameter value
SET  @Update_FY=CASE WHEN @sYear='CFY' THEN  YEAR(GETDATE()) 
WHEN @sYear='PFY1' THEN  YEAR(GETDATE())-1 
WHEN @sYear='PFY2' THEN  YEAR(GETDATE())-2
WHEN @sYear='PFY3' THEN  YEAR(GETDATE())-3   
END 

--PRINT(@Update_FY)

--Storing the Table Name to SysName Variable
SET  @Tbl_Name=CASE WHEN @sYear='CFY' 
                                                       THEN  '[dbo].[TBL_MySample_Source_Data_Cur_FY]'
WHEN @sYear='PFY1' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY1]'
WHEN @sYear='PFY2' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY2]'
WHEN @sYear='PFY3' 
                                                       THEN '[dbo].[TBL_MySample_Source_Data_PrevFY3]'  
END 

--Assigning the Dynamic SQL Statement to a Variable
Set @DynSQL='Select Distinct * From '+ @Tbl_Name

--Next Deleting the respective Year data from the Reporting Table
Delete from  [dbo].[TBL_TBL_MyFinal_Reporting_ Data]   
                                        Where YEAR(Trans_Date )=@Update_FY

-- Next loading the respective Year Source data from a Sample Source Data Table
   INSERT INTO [dbo].[TBL_MyFinal_Reporting_Data]    
   EXEC sp_executesql @DynSQL  
----Eg: SELECT DISTINCT* FROM [dbo].[TBL_MySample_Source_Data_PrevFY2]   

END

GO

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

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts