Filter table using another table result

[Anonymous]

Hello everyone, I'm still learning about DAX queries, so this might be simpler than I think. I need to extract data from a semantic model in Power BI to retrieve all notifications from 2023 to 2025, and I have to do this every week. Then, after obtaining this information, I need to use the extracted notifications to retrieve additional data.

So basically, I'm trying to perform a sub-query in DAX, but I can't find a solution. The query I built runs, but it doesn't seem right, it returns more than 200,000 rows, even though my initial extraction only contained 22,000 notifications.

Is there a way to use the result of one query to filter another in DAX? In my case, I want to filter based on notification numbers from a previously extracted table.

I've used a structure similar to this:

DEFINE

    VAR TempSet =
        SUMMARIZECOLUMNS(
            DimTable[ID],
            FILTER(
                DimTable,
                DimTable[Type] = "Y1"
                    || DimTable[Type] = "Z4"
                    || DimTable[Type] = "Z1"
            ),
            DATESBETWEEN(
                DimTable[Date],
                EDATE( TODAY(), -1 ),
                TODAY()
            )
        )

    VAR ResultSet =
        SUMMARIZECOLUMNS(
            DimTable[ID],
            DimTable[Type],
            DimTable[Description],
            FactTable[TaskCode],
            FactTable[TaskDescription],
            FILTER(
                DimTable,
                DimTable[ID] IN TempSet
            )
        )

EVALUATE
    ResultSet

[Anonymous]

Hi @Anonymous 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @Anonymous 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @Anonymous 
Thank you for reaching out microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided by @BITomS . Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[BITomS · Accepted solution]

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