Forum Discussion
Filter Removal - DAX
Good afternoon folks
I have a calculation in DAX that I want to build very simply, Income - Expense, but I don't want the external filter Expense/Income to influence my DAX, how do I do it?
Below is an image of the filters I want you to consider and what I don't want. So I want to calculate Result = Income - Expense.
5 Replies
If you want to calculate the result as Income - Expense while ignoring external filters on Expense and Income, you can use the following DAX formula:
Result = CALCULATE( SUM('YourTable'[Income]) - SUM('YourTable'[Expense]), ALLEXCEPT('YourTable', 'YourTable'[Result]) )In this formula, replace 'YourTable' with the actual name of your table that contains the Income, Expense, and Result columns.
The SUM('YourTable'[Income]) - SUM('YourTable'[Expense]) calculates the difference between the total income and total expense.
The ALLEXCEPT('YourTable', 'YourTable'[Result]) removes all the filters applied to the table, except for the Result column, ensuring that the calculation is performed independently of any filters on the Expense and Income columns.
This way, the calculated result will not be influenced by external filters on Expense and Income, and it will provide the desired outcome of Income - Expense.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I can't do it this way because my data structure is as per the image below
Amount Column, another expense and income column and etc.
If it's the entire table that should not be impacted by that filter just remove the interaction from the slicer to the visual.
If it's only the last column use the option REMOVEFILTERS in your calculation:
it's only in the column ("measure") of the visual, but the removefilters I can even remove the external revenue/expense filter, but I need to know how much was revenue, how much was expense to subtract revenue - expense and bring it to the visual, do you know how it could be done?
Look what I did in the "Test 1" column as you told me
