@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
