Slowly Changing Dimension and Direct Query

Hi Pawel_1990 ,

In Power BI using DirectQuery mode, creating a calculated column in the Fact Table with complex DAX functions like CALCULATE is not supported because these functions cannot be directly translated into SQL queries. In your scenario, you want to retrieve the correct Index from the Historical_Account_Dim table by matching on Account_ID and checking if the Transaction_Date falls within the Start_Date and End_Date range.

Since you cannot do this using DAX in a calculated column, the recommended workaround is to perform this logic in Power Query (M) using a custom row-by-row join. This can be done by loading both the Fact and Historical tables into Power Query and then writing a custom column that filters the appropriate matching rows from the historical table. After filtering, you can expand the result to pull in the Index column, which will replicate the same outcome as your original DAX code, while staying fully compatible with DirectQuery limitations.

Here’s the Power Query (M) code you can use inside Power BI:

// Step 1: Add a custom column to Fact_Table that filters matching historical records
= Table.AddColumn(Fact_Table, "JoinKey", each 
    Table.SelectRows(Historical_Account_Dim, 
        (h) => [Account_ID] = h[Account_ID] and 
               [Transaction_Date] >= h[Start_Date] and 
               [Transaction_Date] <= h[End_Date]))

// Step 2: Expand the Index column from the JoinKey table
= Table.ExpandTableColumn(#"Previous Step Name", "JoinKey", {"Index"}, {"Historical_Index"})