Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 |
Solved! Go to Solution.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |