Workaround of Except Keyword

[Anonymous]

Currently i am using this measure to get the answer but it is taking too long to load. I have 60 million rows and it takes 20 sec to calculate using performance analyzer. Is there a way around of this EXCEPT keyword in dax so that I can reduce the calculation time?
test 2 =

 VAR Cal =

    CALCULATE (

        [Total Clients],

        EXCEPT (

            CALCULATETABLE (

                VALUES('TABLE'[ID]),

    ISBLANK('TABLE'[ID]) = FALSE(),

   TABLE[TYPE] IN {2,3},

            ),

            CALCULATETABLE (

                VALUES('TABLE'[ID]),

     ISBLANK('TABLE'[ID]) = FALSE(),

    TABLE[TYPE] IN  IN {1,4},

            )

        )

    )

    Return Cal

[Anonymous]

Thanks it worked, and much faster

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

May you can refer to the following measure

test2 =
VAR a =
    FILTER ( ALLSELECTED ( 'TABLE'[ID] ), NOT ( ISBLANK ( 'TABLE'[ID] ) ) )
VAR b =
    CALCULATETABLE ( VALUES ( 'TABLE'[ID] ), 'TABLE'[TYPE] IN { 2, 3 }, a )
VAR c =
    CALCULATETABLE ( VALUES ( 'TABLE'[ID] ), 'TABLE'[TYPE] IN { 1, 4 }, a )
VAR d =
    FILTER ( b, NOT ( 'TABLE'[ID] IN c ) )
RETURN
    CALCULATE ( [Total Clients], d )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak , @Ahmedx , @Ritaf1983