Forum Discussion

ericOnline's avatar
ericOnline
Icon for Post Patron rankPost Patron
6 years ago
Solved

DISTINCT FILTER one table by another table

I'd like to create a new table, derived from filtering an existing table by another existing table using DAX only. (I do not want to use a calculated column). Is this possible?   Example: - I cre...
DAX
Filter two tables
  • HotChilli's avatar
    HotChilli
    6 years ago

    I think this does it. Please test at your side

    TableT5 = CALCULATETABLE(TableT1, NOT( TableT1[THING_ID] IN VALUES(TableT2[THING_ID])))

     

    Also, in Power Query, you could Merge the two tables using a Left Anti Join on THING_ID

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

I think this does it. Please test at your side

TableT5 = CALCULATETABLE(TableT1, NOT( TableT1[THING_ID] IN VALUES(TableT2[THING_ID])))

 

Also, in Power Query, you could Merge the two tables using a Left Anti Join on THING_ID

ericOnline's avatar
ericOnline
Icon for Post Patron rankPost Patron
6 years ago

HotChilli ! Thank you! That worked. Very elegant solution compared to what I found when researching. 
Do you know how to add subsequent conditions?
Example: Want to also exclude BLANKS(). 

Added: && NOT(BLANK()) but empty results still showing. 

Whats odd, is they don't appear to be BLANK() but rather empty strings. I tried && NOT(""), but they still showed up.