CODEWITHSUNDEEP
powerbidaxpowerpivot
Aaron
Aaron

Reputation: 331

DAX Measure for SQL NOT EXISTS

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

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.

Upvotes: 0

Views: 1997

Answers (1)

sergiom
sergiom

Reputation: 4877

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.

Upvotes: 2

Related Questions