Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DRA885
Frequent Visitor

Variable for multiple REMOVEFILTERS

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' )
        )

 

2 REPLIES 2
amitchandak
Super User
Super User

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

 

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors