Filter table using another table result

Hi Anonymous ,

The use of summarizecolumns is problematic because you can't reference a VAR like that in a filter and it means you're trying to use a table to filter another column instead of the exact IDs.

If you use Selectcolumns and Treatas, this should give you what you need:

DEFINE
-- Extract valid notification IDs
VAR NotificationIDs =
SELECTCOLUMNS(
FILTER(
DimTable,
(DimTable[Type] IN {"Y1", "Z4", "Z1"})
&& DimTable[Date] >= DATE(2023, 1, 1)
&& DimTable[Date] <= DATE(2025, 12, 31)
),
"ID", DimTable[ID]
)

-- Get related details from both dimension and fact tables, filtered by those IDs
VAR ResultSet =
CALCULATETABLE(
SELECTCOLUMNS(
FactTable,
"ID", DimTable[ID],
"Type", DimTable[Type],
"Description", DimTable[Description],
"TaskCode", FactTable[TaskCode],
"TaskDescription", FactTable[TaskDescription]
),
TREATAS(NotificationIDs, DimTable[ID])
)

EVALUATE
ResultSet