Filter Removal - DAX

[Anonymous]

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.

[Anonymous]

@MFelix e @Jaywant 

I would need to calculate in a variable what is expense, calculate in another variable what is expenses and return the result = revenue - expense, but disregarding the same column of expense and revenue from the external filter

[MFelix]

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:

https://dax.guide/removefilters/

[Anonymous]

@MFelix 

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

[Jaywant]

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.

Connect with me on LinkedIn

[Anonymous]

@Jaywant 

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.