Simplify DAX to get a listing of customers

[Sullyball2a]

I have a table where there are multiple customer records Table 1 and a table of customers I want to exclude from my results Table2. Tables 1 & 2 are joined in my data model: many to one.

I want a listing (no duplicates) of customers from Table 1 not in Table 2

The DAX I have written to achieve this is:

DEFINE

 VAR MyDetailTbl =

 CALCULATETABLE(

    SELECTCOLUMNS('Table 1'

    ,"Customer Number", 'Table 1'[Customer No]

    ,"isDup", RELATED(Table2[Customer No])

    )

)

VAR MySummaryTbl =

GROUPBY(MyDetailTbl, [Customer Number],[isDup])

EVALUATE

SELECTCOLUMNS(FILTER(MySummaryTbl, ISBLANK([isDup])),"Customer No", [Customer Number])

This seems over complicated. Is there a more straightforward way to achieve this?

[Selva-Salimi · Accepted solution]

Hi @Sullyball2a ,

Yes, you can rewrite it as follows :

except( distinct('Table 1'[Customer No]), distinct( 'MyDetailTbl'[Customer Number]))

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[Sullyball2a · Accepted solution]

Thank you  - I knew there had to be a more simple way. I wasn't aware of the EXCEPT() or DISTINCT() functions. I was aware of VALUES() and I tried it with that instead of DISTINCT() and it worked too.

[Selva-Salimi · Accepted solution]

Hi @Sullyball2a ,

Yes, you can rewrite it as follows :

except( distinct('Table 1'[Customer No]), distinct( 'MyDetailTbl'[Customer Number]))

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.