Problem separating data by location after machines switch places in Power BI

1) Create a mapping table (in Power BI)

This can be:

• Enter Data

• Excel / SharePoint

• CSV

• Dataflow

Example:

Location =
VAR d = Fact[MeasurementDate]
VAR m = Fact[MachineID]
RETURN
CALCULATE (
    MAX ( MachineLocation[Location] ),
    FILTER (
        MachineLocation,
        MachineLocation[MachineID] = m
            && d >= MachineLocation[StartDate]
            && d <= COALESCE ( MachineLocation[EndDate], DATE ( 9999, 12, 31 ) )
    )
)

The solution above is given this way because you told you cannot change anything in DB, if you could the best approach would be adding ID to Fact and Type-2 Slowly Changing Dimension as a new dimention and use those by giving relation between them.

Hi @effi_go,

  and  are completely correct; this is a classic Slowly Changing Dimension (SCD) Type 2 scenario. Since you cannot modify the database, creating this "Logic Layer" inside Power BI is the only way to solve it.Here is the "Complete" guide to implementing this, including the visual logic and a performance tip for the DAX formula.

1. The Concept: Time-Based Lookup

You are moving from a Static relationship (Machine = Location) to a Temporal relationship (Machine + Time = Location).

You need a standalone "Mapping Table" that defines the "Valid From" and "Valid To" dates for each machine's location.

2. The Implementation (DAX Calculated Column)

The DAX solution provided by @cengizhanarslan is generally the most performant method for this "Range Lookup" scenario in Power BI, as Power Query lookups can be very slow with large datasets.

However, to make the formula robust against missing End Dates (active records), use this refined pattern:

Step 1: Create the Mapping Table (Use "Enter Data" or an Excel sheet as suggested)

• MachineID | Location | StartDate | EndDate

• A | X | 1/1/2020 | 6/30/2024

• A | Y | 7/1/2024 | 12/31/9999 (Use a far future date for "Active")

  Step 2: The Calculated Column Add this column to your Fact Table (where your measurements are). Note: Ensure there is NO active relationship between your Fact Table and the Mapping Table.

   

  Calculated Location = 
  VAR CurrentDate = 'FactTable'[MeasurementDate]
  VAR CurrentMachine = 'FactTable'[MachineID]
  RETURN
      CALCULATE (
          // We take MAX to return the single text value found
          MAX ( 'LocationMapping'[Location] ),
          FILTER (
              'LocationMapping',
              'LocationMapping'[MachineID] = CurrentMachine &&
              'LocationMapping'[StartDate] <= CurrentDate &&
              // Handle NULL EndDates as "Today" or Future
              COALESCE('LocationMapping'[EndDate], DATE(9999,12,31)) >= CurrentDate
          )
      )

  3. Why not Power Query?

    mentioned that doing this in Power Query "could get slow fast". This is because performing a "Non-Equi Join" (joining on a date range rather than an exact match) forces Power Query to scan the entire mapping table for every single row in your Main Table. DAX (specifically the VertiPaq engine) is much faster at handling these in-memory range filters.

  Summary Checklist

  1. Create the Mapping Table with Start/End dates.

  2. Use 12/31/9999 for the End Date of the current location.

  3. Use the Calculated Column approach (not a Measure) so you can use the Location as a slicer/axis in your charts.

     ---

     If this breakdown helps clarify the "SCD Type 2" implementation, a Kudos is appreciated!
     This response was assisted by AI for translation and formatting purposes.

  4.  

  5.  

  tayloramy

•  

•  

cengizhanarslan