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

Order: The order number
Value: The order value
Index1: Row Index total
Index2: Row Index, which should be replaced

As you can see in order 103, there are two rows in (Index1: 4 &5). The booking with Index1 = 5, is the correction booking. That means that the row with Index1= 4 should be filtered out. The SQL code I am using to filter all false entries is this one:

SELECT DBA1.Order,
       DBA1.Value  
FROM   AZ.SC DBA1
WHERE NOT EXISTS (SELECT * 
                       FROM   AZ.SC DBA2 
                       WHERE  DBA2.INDEX2 = DBA1.INDEX1)

Since this SQL will not allow "Query Folding" which is necessary for PBI Incremental refresh, I need somehow an approach within a DAX Measure, that will filter also all false entries. But how?

Please let me know if you need further information.

Are you sure you're after DAX code, not the Power Query language, M? — Alexis Olson
Thanks for your reply @Alexis Olson. I am looking for an approach that will not break Query Folding — joshua
Actually the problem is not only the query folding. I don't think that incremental updates can handle deletion for the rows that where imported in previous refreshes. — sergiom

sergiom sergiom · Accepted Answer · 2021-01-24T17:08:04

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.

DAX Measure for SQL NOT EXISTS

1 Answers