DAX Expression to remove an existing filter and apply new one

[rbhattacharya]

Hello,

I have a Date filter in the report where I'm selecting current month. I have a requirement where I will have to calculate a certain measure with a new filter value i.e. remove the current filter and reapply a new filter. I'm restricted to using a single filter in the report which is set to current month. I'm using the following DAX expression currently where my current filter is set to "January" and I'm trying to retrieve data for "December":

TechnicalEmpHeadcountProjection = CALCULATE([Technical Employee Headcount *], FILTER(ALL('Budgeting v_IncomeStatement'[PeriodFormatted].[Month]), 'Budgeting v_IncomeStatement'[PeriodFormatted].[Month]= "December"))

The above DAX expression doesn't seem to work. [PeriodFormatted] is my Date column. I'm also attaching a screenshot.

Kindly assist me with the DAX.

Regards,

RB

[Jon_vB]

I had a similar issue where I wanted to remove the filter on Employee Type, but add a new filter on Appointment Type - so that whereever this measure is placed, it will show the performance of one employee type on a set of Appointment types.  If it's in a viz with the Month column or Day, or Employee name - I want all those filters to remain. 

I found that if you use ALL(), ALLSELECTED() or if you REMOVEFILTERS, then add a FILTER statement (as in a Calculate with 2 filters) --- it would never work.

Just having the REMOVEFILTERS worked for getting the Employee Type filter off, however when I add the second "Filter" to the calculate, it broke.

I was able to make it work by Filtering the table with my NEW filter requirement First (inner most function), then using Calculate to remove the filter I don't want.

CALCULATE(SUMX(FILTER(Appointments, NOT(LEFT([ApptType], 2) IN {"PT","OT"})) 
               , Appointments[IsReportable] )  
          ,REMOVEFILTERS(StaffResources[EmployeeType]) 
         )

[LL11SJL]

thanks! just used this - helped hugely!!😀

[lbendlin]

Have you tried using REMOVEFILTERS()  or a disconnected table to feed your slicer?

[rbhattacharya · Accepted solution]

Hello,

I was able to use "ALL" and remove existing filter. Then apply new filter to this solved the issue. Took me some time to figure out the DAX. Listing the new DAX for reference. 

TechnicalEmpHeadcountProjection = CALCULATE (SUM('Budgeting v_IncomeStatement'[TechnicalEmpHeadcountRevisedCol]), ALL('Budgeting v_IncomeStatement'[PeriodFormatted].[Month]), 'Budgeting v_IncomeStatement'[PeriodFormatted].[Month] = "December")

Regards,

RB