DAX技术请求：创建带筛选的CrossJoin表实现多表关联

Got it, let's solve this. Since you can't use ETL and Power Query doesn't support this kind of conditional join natively, a DAX calculated table using GENERATE (instead of CROSSJOIN for better performance) is exactly what you need. This will iterate over each row in Table A and pull only the matching error rows from Table 2 where the error date falls within the row's start/end range.

Here's the full DAX formula to create your desired table:

Filtered Error Table = 
SELECTCOLUMNS(
    GENERATE(
        'Table A',
        FILTER(
            'Table 2',
            'Table 2'[Date] >= 'Table A'[StartDate] && 
            'Table 2'[Date] <= 'Table A'[EndEnd]  -- Note: Table A uses "EndEnd" column; we'll rename it to "EndDate" below
        )
    ),
    "Index", 'Table A'[Index],
    "StartDate", 'Table A'[StartDate],
    "EndDate", 'Table A'[EndEnd],  -- Rename Table A's "EndEnd" to match your desired output column name
    "DateError", 'Table 2'[Date],
    "Error", 'Table 2'[Error]
)

How this works: #

• GENERATE: This function acts like a row-level "cross apply"—it takes each row from Table A, then runs the inner expression (the filtered Table 2) for that specific row. This is way more efficient than CROSSJOIN (which creates all possible combinations first then filters) especially for large datasets from your ADSL/ODATA sources.
• FILTER: For each row in Table A, we keep only the rows from Table 2 where the Date is between the current row's StartDate and EndEnd.
• SELECTCOLUMNS: Shapes the output to match your exact desired structure—renaming columns (like EndEnd to EndDate and Date to DateError) and selecting only the columns you need.

Output Verification: #

When you create this calculated table, it will exactly match the result you're looking for:

Just make sure to replace 'Table A' and 'Table 2' with the actual names of your tables in your model.

内容的提问来源于stack exchange，提问作者Harry Leboeuf