powerbi - DAX Measure for SQL NOT EXISTS

Question

I would like to add an incremental refresh for one of our biggest transactional tables. This transactional table has this structure:

Order	Value	Index1	Index2
100	5	1	0
101	5	2	0
102	6	3	0
103	2	4	0
103	3	5	4
104	4	6	0

question from:https://stackoverflow.com/questions/65869963/dax-measure-for-sql-not-exists

Answer 1

If your idea is to import all of the rows and later filter the fact table keeping only the updated ones, a possible solution is to add a calculated column to be used in any measure that uses the fact table, stating if the row is to be considered or not. This can be achieved for instance with the following DAX code

IsValid = 
VAR CurrentIndex = CALCULATETABLE( VALUES( SC[Index1] ) )
VAR RemovedIndexes = ALL( SC[Index2] )
RETURN
    IF ( 
        ISEMPTY( INTERSECT(CurrentIndex, RemovedIndexes) ), 
        1, 
        0 
    )

Otherwise, if the idea is to compute a calculated table with the older rows filtered out a possible implementation is

SC Filtered = 
VAR AllIndexes = ALL( SC[Index1] )
VAR RemovedIndexes = ALL( SC[Index2] )
VAR ValidIndexes = EXCEPT( AllIndexes, RemovedIndexes )
RETURN
    SUMMARIZECOLUMNS(
        SC[Order],
        SC[Value],
        TREATAS( ValidIndexes, SC[Index1] )
    )

But this might waste a lot of memory, since it almost duplicates the fact table.