topic DISTINCT FILTER one table by another table in DAX Commands and Tips

DISTINCT FILTER one table by another table

ericOnline — Thu, 23 Apr 2020 00:06:30 GMT

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 created a relationship between T1[ID] and T2[ID]

T1: (existing)

THING_ID	VAL	VAL2
1	X	Y
1	X	X
2	Y	Y
9	Y	Z
9	Y	Y

T2: (existing)

THING_ID	VAL	VAL2
1	A	AA
2	B	BB
3	C	CC
4	D	DD
5	E	EE

Something like:

T3 = DISTINCT( SUMMARIZE( FILTER( T1, T1[ID] NOTIN T2[ID] ), T1[ID], T1[VAL] ) )

Desired Results:

T3: (new)

THING_ID	VAL
9	Y
9	Y

Re: DISTINCT FILTER one table by another table

HotChilli — Thu, 23 Apr 2020 01:00:31 GMT

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

Re: DISTINCT FILTER one table by another table

ericOnline — Thu, 23 Apr 2020 01:17:45 GMT

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.