Optimizing Left Anti in DAX Query

[smpa01]

Hi,

My source data is following.

Table Name _dim

_dimID	_dimName	_dimAddr
A1	Name1	Addr1
A2	Name2	Addr2
A3	Name3	Addr3
A4	Name4	Addr4
A5	Name5	Addr5
A6	Name6	Addr6
A7	Name7	Addr7
A8	Name8	Addr8
A9	Name9	Addr9
A10	Name10	Addr10

Table Name _factAddr

_factAddress

Addr2

Addr4

Addr6

Table Name _factNm

_factName

Name1

Name1

Name4

Name4

Name4

Name8

Name8

There is no relationship between this 3 tables. I want to end up with a _dim table that does not have _factAddress from _factAddr and _factName from _factNm like following

_dimID	_dimName	_dimAddr
A3	Name3	Addr3
A5	Name5	Addr5
A7	Name7	Addr7
A9	Name9	Addr9
A10	Name10	Addr10

In reality, I am performing this on a _dim with 8M+ rows , wirh _factNm and _factAddr with respectively 1M+ and 5M+ rows. I want to optimize my query and so far I came up with following

EVALUATE
--option1--
VAR _01 =
    FILTER (
        NATURALINNERJOIN (
            _dim,
            EXCEPT ( VALUES ( _dim[_dimName] ), VALUES ( _factNm[_factName] ) )
        ),
        NOT _dim[_dimAddr] IN VALUES ( _factAddr[_factAddress] )
    )
--option2--    
VAR _02 =
    CALCULATETABLE (
        _dim,
        EXCEPT ( VALUES ( _dim[_dimName] ), VALUES ( _factNm[_factName] ) ),
        EXCEPT ( VALUES ( _dim[_dimAddr] ), VALUES ( _factAddr[_factAddress] ) )
    )
--option3--    
VAR _03 =
    FILTER (
        _dim,
        NOT _dim[_dimName]
            IN VALUES ( _factNm[_factName] )
                && NOT _dim[_dimAddr] IN VALUES ( _factAddr[_factAddress] )
    )
RETURN
    _01

In terms of total query duration, Option1 takes the least duration (option 1<2<3). Is there a better way of other than above to reach my goal?

Thank you in advance.

[parry2k · Accepted solution]

@smpa01 cannot test the performance but can you try this, change column name as per your model.

New Dim = 
VAR __table = 
UNION ( 
CALCULATETABLE ( Dim,  TREATAS ( VALUES ( FactAddress[Address] ), Dim[_dimAddr] ) ),
CALCULATETABLE ( Dim,  TREATAS ( VALUES ( FactName[Name] ), Dim[_dimName] ) )
)
RETURN EXCEPT ( Dim, __table )