Forum Discussion
Solved
Making Measures Ignore Filters
Hi Everyone,
Here is a simplified version of my table called 'Employee Performance'
| Employee | Location | Shift | Task Type | Task Value | Work Volume | Report Date |
| John Doe | Office | First Rotation | AB | 0.75 | 1 | 10/10/2021 |
| Jane Doe | Home | Second Rotation | AB | 1 | 2 | 10/10/2021 |
| Batman | Bat Cave | Third Rotation | CD | 0.25 | 1 | 10/10/2021 |
| John Doe | Office | First Rotation | CD | 0.75 | 2 | 10/10/2021 |
| Batman | Bat Cave | Third Rotation | EF | 0.25 | 3 | 10/10/2021 |
| John Doe | Home | Third Rotation | AB | 0.75 | 1 | 10/11/2021 |
| Jane Doe | Bat Cave | First Rotation | AB | 1.5 | 2 | 10/11/2021 |
| Batman | Office | Second Rotation | CD | 0.5 | 6 | 10/11/2021 |
| John Doe | Home | Third Rotation | EF | 0.75 | 1 | 10/11/2021 |
| John Doe | Bat Cave | First Rotation | CD | 0.75 | 2 | 10/12/2021 |
| Jane Doe | Office | Third Rotation | EF | 0.75 | 3 | 10/12/2021 |
| Batman | Home | Second Rotation | AB | 1 | 4 | 10/12/2021 |
| John Doe | Bat Cave | First Rotation | AB | 0.75 | 2 | 10/12/2021 |
| John Doe | Office | First Rotation | CD | 1.5 | 3 | 10/13/2021 |
| Jane Doe | Home | Third Rotation | EF | 0.5 | 1 | 10/13/2021 |
| Batman | Bat Cave | Second Rotation | AB | 0.25 | 4 | 10/13/2021 |
| Jane Doe | Home | Third Rotation | CD | 0.25 | 2 | 10/13/2021 |
| Batman | Bat Cave | Second Rotation | US | 0.75 | 1 | 10/13/2021 |
| Ned Flanders | Home | Third Rotation | US | 0.33 | 2 | 10/14/2021 |
| Bart Simpson | Office | First Rotation | AB | 1 | 3 | 10/14/2021 |
| John Doe | Home | Third Rotation | EF | 1 | 1 | 10/15/2021 |
| Jane Doe | Bat Cave | Second Rotation | CD | 0.25 | 2 | 10/15/2021 |
| Batman | Office | First Rotation | CD | 0.5 | 1 | 10/15/2021 |
| John Doe | Home | Second Rotation | AB | 0.5 | 1 | 10/16/2021 |
| John Doe | Bat Cave | First Rotation | EF | 0.25 | 4 | 10/17/2021 |
| Batman | Office | Third Rotation | AB | 0.75 | 2 | 10/17/2021 |
| John Doe | Bat Cave | Second Rotation | CD | 0.75 | 6 | 10/17/2021 |
| Jane Doe | Office | First Rotation | AB | 0.25 | 1 | 10/17/2021 |
| Batman | Home | Third Rotation | EF | 0.75 | 1 | 10/17/2021 |
| Jane Doe | Office | Third Rotation | US | 0.75 | 2 | 10/18/2021 |
| Batman | Home | Second Rotation | CD | 0.33 | 2 | 10/18/2021 |
| John Doe | Office | First Rotation | AB | 1 | 1 | 10/18/2021 |
I'm trying to return the total work volume for the last 3 days by location.
Here's the DAX I have:
WeeklySumByLocation =
CALCULATE
(
SUM('Employee Performance'[Work Volume]),
VALUES('Employee Performance'[Location]),
FILTER
(
'Employee Performance',
DATE ( YEAR ( NOW () ), MONTH ( NOW () ), DAY ( NOW () - 2)) <= MAX('Employee Performance'[Report Date])
)
)
It returns 27 for Location = BatCave, but it should only return 10. Why?
Also, I'd like to display this total for the last 3 days on a graph that simultaneously displays the total for a user-defined date range via a page-level filter. How do I get this DAX formula to ignore that page-level filter?
Also, I'd like to display this total for the last 3 days on a graph that simultaneously displays the total for a user-defined date range via a page-level filter. How do I get this DAX formula to ignore that page-level filter?
3 Replies
Hi mccollough
Currently, your measure looks to be returning the number of rows (i.e. 27).
Can you give the following a go (adjust Table Names to your relevant table name... I called mine tblBatMan :D) I've also added the "ALL ('tblBatman')" at the end to ignore filters 🙂
mea_New = VAR _StartDate = LASTDATE ( 'tblBatMan'[Report Date] ) VAR _EndDate = MAX ( tblBatMan[Report Date] ) - 2 RETURN CALCULATE ( SUM ( tblBatMan[Work Volume] ) , FILTER ( 'tblBatMan' , _StartDate <= MAX ( 'tblBatMan'[Report Date] ) && 'tblBatMan'[Report Date] >= _EndDate ) , FILTER ('tblBatMan' , tblBatMan[Location] = "Bat Cave" ) , ALL ('tblBatman') )You should get 10 🙂
That worked splendidly! One last question though, I noticed that the page filter will still effect the measure if the number of days passed is less than 3. Any idea how to make it ignore that?
