Forum Discussion

ReadTheIron's avatar
ReadTheIron
Helper III
3 years ago
Solved

CalculateTable filtered by another table

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:

FailureIDType
1AGreen
1BBlue
1CGreen
1DGreen

 

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.
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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