CalculateTable filtered by another table

[ReadTheIron]

I have two tables, FailureTable and DisqualifiedTable. FailureTable has millions of entries, but I only want to work with a small subset of that table. I've created a calculated table based on FailureType, and I want to filter that table further by eliminating individual entries that are listed in DisqualifiedTable

FailureTable:

FailureID	Type
1A	Green
1B	Blue
1C	Green
1D	Green

DisqualifiedTable:

FailureID

1C

1B

I tried

WorkingTable = CALCULATETABLE('FailureTable', FILTER('FailureTable','FailureTable'[Type]="Green"), EXCEPT(VALUES('FailureTable'[FailureID]),VALUES('DisqualifiedTable'[FailureID])))

However, this gives me the error "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

The query works just fine before I add the "EXCEPT", and the table I want  - with the disqualified failures removed - will be smaller than the table just filtered by type. I think I'm doing this in the wrong order, but I'm not sure how to fix it.

[v-yiruan-msft · Accepted solution]

Hi @ReadTheIron ,

You can update the formula of calculated table 'WorkingTable' as below and check if it will return the expected result.... Please find the details in the attachment.

WorkingTable =
CALCULATETABLE (
    'FailureTable',
    FILTER (
        'FailureTable',
        'FailureTable'[Type] = "Green"
            && NOT ( 'FailureTable'[FailureID] IN VALUES ( 'DisqualifiedTable'[FailureID] ) )
    )
)

Best Regards