Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
elcojo2
Frequent Visitor

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.

1 ACCEPTED SOLUTION
AnalyticsWizard
Solution Supplier
Solution Supplier

@elcojo2 

 

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.

View solution in original post

2 REPLIES 2
AnalyticsWizard
Solution Supplier
Solution Supplier

@elcojo2 

 

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.

Ritaf1983
Super User
Super User

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.