Making Measures Ignore Filters

[mccollough]

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?

[CNENFRNL · Accepted solution]

[mccollough]

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?

[TheoC]

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 🙂