Forum Discussion
Helper IIIAdd a filter to omit specific results
Hi,
I have a measure that calculates the sum of sales per person per year. How can I add a filter to the dax code to OMIT a SPECIFIC PERSON'S sales for a date range i.e.:
| Sales Person | Date | Amount |
| John Smith | 2022 | 10000 |
| John Smith | 2021 | 10000 |
| John Smith | 2020 | 10000 |
| John Smith | 2019 | 10000 |
| James Brown | 2022 | 8000 |
| James Brown | 2021 | 8000 |
| James Brown | 2020 | 8000 |
| James Brown | 2019 | 8000 |
I want to calculate the sum of total sales for both persons but I want to exclude all sales for James brown prior to 2022.
so the total sales would be = 48,000. 40,000 to John Smith and 8,000 to James Brown.
I thought about using CALCULATE (SUM(SALES),FILTER() etc but i'm not sure what to write in the filter.
The FILTER function is used to exclude sales from James Brown prior to 2022 by checking if the Sales Person is not equal to "James Brown" or if the Date is greater than or equal to "2022". The ALL function is used to ensure that the filter is applied to all data in the SALES table. The CALCULATE function then calculates the sum of sales for the filtered data.
Let me know if you require further assistance.
3 Replies
- Sahir_Maharaj
Super User
Hello Joseph_Hchaime,
You can add a filter to the DAX formula using the CALCULATE function along with the FILTER function. Here's an example of how to do this:
Total Sales Excluding James Brown Prior to 2022 = CALCULATE( SUM(SALES[Amount]), FILTER( ALL(SALES), SALES[Sales Person] <> "James Brown" || SALES[Date] >= "2022" ) ) - Sahir_Maharaj
The FILTER function is used to exclude sales from James Brown prior to 2022 by checking if the Sales Person is not equal to "James Brown" or if the Date is greater than or equal to "2022". The ALL function is used to ensure that the filter is applied to all data in the SALES table. The CALCULATE function then calculates the sum of sales for the filtered data.
Let me know if you require further assistance.
Joseph_HchaimeAppreciate the detailed explanation!