Variable for multiple REMOVEFILTERS

DRA885 — Tue, 14 Feb 2023 04:08:30 GMT

Is it possible to write a variable that references multiple REMOVEFILTERS i.e. duplicate this but use the variable instead?

CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = rank1, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) )

Re: Variable for multiple REMOVEFILTERS

amitchandak — Tue, 14 Feb 2023 15:43:03 GMT

DRA885 , I doubt that you have use table or column names.

But you can have removefilters in a var and use in calculate

Re: Variable for multiple REMOVEFILTERS

DRA885 — Tue, 14 Feb 2023 21:38:58 GMT

amitchandak

'Capsule Descriptions', 'Cost Centers', 'Discipline Groups' & 'Project Listing' are table names.

So to ask my question again, how do I write a VAR for the REMOVEFILTERS part of the code.

For context, this is the full measure:

Proposal Rated = VAR ActualFilter = FILTER ( 'Actual & Forecast Project Resources', 'Actual & Forecast Project Resources'[Full or Rated Hrs] = "Rated" && 'Actual & Forecast Project Resources'[Consolidated Hours Type] = "Proposal HOS's" ) VAR Rank1 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter ) VAR Result1 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 1] = "Rank 1 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = rank1, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank2 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, 'Proposal Table'[Prospect Rank] <> Rank1 ) VAR Result2 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 2] = "Rank 2 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank2, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank3 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2 } ) ) VAR Result3 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 3] = "Rank 3 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank3, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank4 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3 } ) ) VAR Result4 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 4] = "Rank 4 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank4, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank5 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4 } ) ) VAR Result5 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 5] = "Rank 5 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank5, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank6 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4, Rank5 } ) ) VAR Result6 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 6] = "Rank 6 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank6, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank7 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4, Rank5, Rank6 } ) ) VAR Result7 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 7] = "Rank 7 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank7, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank8 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4, Rank5, Rank6, Rank7 } ) ) VAR Result8 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 8] = "Rank 8 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank8, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank9 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4, Rank5, Rank6, Rank7, Rank8 } ) ) VAR Result9 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 9] = "Rank 9 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank9, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Rank10 = CALCULATE ( MIN ( 'Proposal Table'[Prospect Rank] ), ActualFilter, NOT ( 'Proposal Table'[Prospect Rank] IN { Rank1, Rank2, Rank3, Rank4, Rank5, Rank6, Rank7, Rank8, Rank9 } ) ) VAR Result10 = IF ( [HT Max] = "Proposal HOS's" && [Sel value 10] = "Rank 10 Rated", CALCULATE ( [FTE Sum], ActualFilter, 'Proposal Table'[Prospect Rank] = Rank10, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ) ), BLANK () ) VAR Result10Plus = IF ( [HT Max] = "Proposal HOS's" && [Sel value 10+] = "Rank 10+ Rated", CALCULATE ( [FTE Sum], ActualFilter, REMOVEFILTERS ( 'Capsule Descriptions' ), REMOVEFILTERS ( 'Cost Centers' ), REMOVEFILTERS ( 'Discipline Groups' ), REMOVEFILTERS ( 'Project Listing' ), 'Proposal Table'[Prospect Rank] > IF ( rank10 = BLANK (), "", rank10 ) ), BLANK () ) RETURN Result1 + Result2 + Result3 + Result4 + Result5 + Result6 + Result7 + Result8 + Result9 + Result10 + Result10Plus

Just creating the VAR ActualFilter massively improved the run time of this measure (4500 m/s to 500m/s) so I was hoping that utilising another variable for the REMOVEFILTERS might offer similar gains in efficiency

topic Variable for multiple REMOVEFILTERS in DAX Commands and Tips

Variable for multiple REMOVEFILTERS

Re: Variable for multiple REMOVEFILTERS

Re: Variable for multiple REMOVEFILTERS