Forum Discussion
Measure not affected by filter
I have a measure that calculates the correct value, but i do not want to change when added I added to a matrix. Also there are filters affecting that matrix on the page. How can I add a measure to matrix without being affected by filters.
I am new to power bi.
thanks.
In Power BI, if you want a measure to ignore certain filters, you can make use of DAX functions like `CALCULATE` along with filter-modifying functions such as `ALL`, `ALLEXCEPT`, `ALLSELECTED`, `REMOVEFILTERS`, etc. The choice of function depends on the context and the specific filters you want the measure to ignore.
Let’s say you have a measure called `Total Sales` and you want to create a new measure that calculates the `Total Sales` for all data regardless of any filters applied to the matrix visual. You would create a new measure like this:
```dax
Total Sales Unfiltered = CALCULATE([Total Sales], ALL('TableName'))
```In this measure, `ALL('TableName')` is removing any filters that might be applied to the 'TableName' table. If you only want to remove the filters from a specific column, you could use `ALL('TableName'[ColumnName])` instead.
If you want to keep some slicer or page-level filters and only ignore filters from within the matrix, you could use `ALLEXCEPT` like this:
```dax
Total Sales SlicerUnaffected = CALCULATE([Total Sales], ALLEXCEPT('TableName', 'TableName'[SlicerColumn]))
```Here, `[SlicerColumn]` would still affect the measure, but other filters on 'TableName' would not.
When you add this new measure to the matrix, it will display the same value across all rows/columns because it's calculating the total sales independently of the filters applied directly to the matrix.
Remember, when using these functions, especially `ALLSELECTED`, it can be affected by slicers or other visuals on the page that interact with the same data. `ALLSELECTED` can be used if you still want the visual to be responsive to slicers that the user interacts with.
Example with `ALLSELECTED`:
```dax
Total Sales AllSelected = CALCULATE([Total Sales], ALLSELECTED('TableName'))
```This measure will respect all the filters and slicers selected by the user but will ignore any row-level filters applied by the matrix itself.
For your specific case, consider which context you want to maintain and which you want to ignore, then apply the appropriate DAX function. As you are new to Power BI, I'd recommend exploring each of these functions further to understand their behavior fully.
2 Replies
Hi elcojo2
For ignore filters you can use the dax calculate function + all / remove filters.Please refer to the linked article for more information:
https://databear.com/all-and-removefilters-in-power-bi/
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
In Power BI, if you want a measure to ignore certain filters, you can make use of DAX functions like `CALCULATE` along with filter-modifying functions such as `ALL`, `ALLEXCEPT`, `ALLSELECTED`, `REMOVEFILTERS`, etc. The choice of function depends on the context and the specific filters you want the measure to ignore.
Let’s say you have a measure called `Total Sales` and you want to create a new measure that calculates the `Total Sales` for all data regardless of any filters applied to the matrix visual. You would create a new measure like this:
```dax
Total Sales Unfiltered = CALCULATE([Total Sales], ALL('TableName'))
```In this measure, `ALL('TableName')` is removing any filters that might be applied to the 'TableName' table. If you only want to remove the filters from a specific column, you could use `ALL('TableName'[ColumnName])` instead.
If you want to keep some slicer or page-level filters and only ignore filters from within the matrix, you could use `ALLEXCEPT` like this:
```dax
Total Sales SlicerUnaffected = CALCULATE([Total Sales], ALLEXCEPT('TableName', 'TableName'[SlicerColumn]))
```Here, `[SlicerColumn]` would still affect the measure, but other filters on 'TableName' would not.
When you add this new measure to the matrix, it will display the same value across all rows/columns because it's calculating the total sales independently of the filters applied directly to the matrix.
Remember, when using these functions, especially `ALLSELECTED`, it can be affected by slicers or other visuals on the page that interact with the same data. `ALLSELECTED` can be used if you still want the visual to be responsive to slicers that the user interacts with.
Example with `ALLSELECTED`:
```dax
Total Sales AllSelected = CALCULATE([Total Sales], ALLSELECTED('TableName'))
```This measure will respect all the filters and slicers selected by the user but will ignore any row-level filters applied by the matrix itself.
For your specific case, consider which context you want to maintain and which you want to ignore, then apply the appropriate DAX function. As you are new to Power BI, I'd recommend exploring each of these functions further to understand their behavior fully.
