Home » REMOVEFILTERS Function DAX

REMOVEFILTERS Function DAX

REMOVEFILTER function is a Power BI filter function in DAX, It removes filters from the specified tables or columns.

It can only be used to remove filters but not to return a table.



SYNTAX

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])
table Is a table that you want to remove filters on.
column Is the column that you want to remove filters on.

Lets take a look at using REMOVEFILTERS function in Power BI.

Here we have a sample dataset named ItemDetails as shown below.

Item Color Sales_Qty
Iphone 5s Gray 1573
Iphone 5s Sliver 4252
Iphone 5s Gold 6352
Iphone 7 Gray 6226
Iphone 7 Black 1453
Iphone 7 Glod 4633
Galaxy S21 5G Phantom Black 5222
Galaxy S21 5G Phantom Silver 5256
Galaxy S10 Diamond Silver 6252
Galaxy S10 Cardinal Red 7326
Galaxy S10 Black 6366

Lets create a simple report that display the total quantity sold by item and color as shown below.

Lets assume that, you want to calculate the percentage of sales contribution by each color for individual item, so to do that you need to divide sales quantity of each color within individual item with over all sales quantity of that individual item.



You also know that filter context is also applied on Item and Color level in above report, that’s why it returns a sales quantity by Item and Color, so to get the total sales for individual item regardless color, you need to remove filter context on color level, that you can do using a REMOVEFILTER function as shown below.

OverAllSales =
CALCULATE ( SUM ( ItemData[Sales_Qty] ), REMOVEFILTERS ( ItemData[Color] ) )

After creating a measure, lets drag the measure into report to see the output.

You can see REMOVEFILTES function removes the filter context from Color column and returns the over all sales of an individual Item against each rows of color.

Now to calculate the percentage of sales contribution, you need to use DIVIDE function which will divide sales quantity of each color with over all sales of an individual item that is returned by DAX OverAllSales . 

Lets create another DAX named SalesContribution% as shown below.

SalesContribution% =
DIVIDE( SUM(ItemData[Sales_Qty]), [OverAllSales])

Lets drag this measure into report to see the output.





You can see, for individual item the total percentage of sales contribution is 100% and each color has some sales contribution which makes the total percentage of sales contribution as 100%.

So far you have seen, total sales contribution by each color for individual item.

Lets see the total sales contribution by each item in over all Item sales. If you collapse the matrix visual at item level in order to see the percentage of sales contribution of each item in over all sales, you will see that it displays 100% sales contribution by each item in over all sales, which is clearly wrong, how it could be possible that each individual item has contributed as 100 % of over all sales.

So to show correct contribution of each individual item in over all item sales you need to remove filter context on Item Level.

Lets quickly create a measure OverAllSalesByItem , which returns over all sales by Item against each rows of items, following measure uses a REMOVEFILTERS function which removes the Item Level filter context.

OverAllSalesByItem =
CALCULATE ( SUM ( ItemData[Sales_Qty] ), REMOVEFILTERS ( ItemData[Item]) )
Now lets create one more measure SalesContribution%_BYItem that will calculate the percentage.
SalesContribution%_BYItem =
DIVIDE( SUM(ItemData[Sales_Qty]), [OverAllSalesByItem])
Lets drag these two measures OverAllSalesByItem  and SalesContribution%_BYItem into report to see the output.

You can see, the sales contribution by individual item to over all item sales, and you can also note that the total sales contribution of each individual item makes the total sales contribution as 100 %.




Also Read..

ALL

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




 2,336 total views,  1 views today

Leave a Reply

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