Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ericOnline
Post Patron
Post Patron

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

 

T1: (existing)

THING_IDVALVAL2
1XY
1XX
2YY
9YZ
9YY

 

T2: (existing)

THING_IDVALVAL2
1AAA
2BBB
3CCC
4DDD
5EEE

 

Something like:

 

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

 

 

Desired Results:

T3: (new)

THING_IDVAL
9Y
9Y
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

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 ! 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. 

image.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.