Hello, I'm having difficulties to define a solution to the following problem. I'm trying to join 2 tables but using different data of the first table depending on a date from the second table.Let me explain. I have a list of sensors and their properties who can be moved from a location to another. The table is built on the following model : Sensor IDLocationAttributeInstallation dateUninstallation dateID#1Location #1501/01/202101/15/2021ID#2Location #2701/01/2021 ID#1Location #3401/16/2021 ID#3Location #4501/01/2021 The second table is a table of measures built on the folowing model Sensor IDDate of measureMeasureID#101/03/20211.2ID#201/05/20212.1ID#101/14/20211.6ID#101/17/20211.5ID#301/19/20211.4ID#101/24/20211.5ID#201/27/20211.7 I would like to add the "Location" and the "Attribute" of the first table to the corresponding measure in the second table depending on the date of the measure.If the "Uninstallation Date" in the first table is not completed, the sensor is supposed to be still installed at this location. The result should look like this : Sensor IDDate of measureMeasureLocationAttributeID#101/03/20211.2Location #15ID#201/05/20212.1Location #27ID#101/14/20211.6Location #15ID#101/17/20211.5Location #34ID#301/19/20211.4Location #45ID#101/24/20211.5Location #34ID#201/27/20211.7Location #27 How can I achieve this in PowerBI ? Thank You. Charles

Hi Charlesdudu ,According to your needs, based on the date as the judgment condition, two measures are created to get the correct results. Refer to the following:nlocation = VAR _sensorid = SELECTEDVALUE ( 'Table2'[Sensor ID] ) VAR _curdate = SELECTEDVALUE ( 'Table2'[Date of measure] ) VAR _insdate = CALCULATE ( MAX ( 'Table1'[Installation date] ), FILTER ( 'Table1', 'Table1'[Sensor ID] = _sensorid && 'Table1'[Uninstallation date] <> BLANK () ) ) VAR _uinsdate = CALCULATE ( MAX ( 'Table1'[Uninstallation date] ), FILTER ( 'Table1', 'Table1'[Sensor ID] = _sensorid && 'Table1'[Uninstallation date] <> BLANK () ) ) VAR _insdate1 = CALCULATE ( MAX ( 'Table1'[Installation date] ), FILTER ( 'Table1', 'Table1'[Sensor ID] = _sensorid && 'Table1'[Uninstallation date] = BLANK () ) ) VAR _location = CALCULATE ( MAX ( 'Table1'[Location] ), FILTER ( 'Table1', 'Table1'[Sensor ID] = _sensorid && 'Table1'[Uninstallation date] <> BLANK () ) ) VAR _location1 = CALCULATE ( MAX ( 'Table1'[Location] ), FILTER ( 'Table1', 'Table1'[Sensor ID] = _sensorid && 'Table1'[Uninstallation date] = BLANK () ) ) RETURN IF ( _curdate >= _insdate && _curdate <= _uinsdate, _location, _location1 )Here is the sample pbix file.If the problem is still not resolved, please provide detailed error information. Let me know immediately, looking forward to your reply. Best Regards,Henry If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Charlesdudu

Regular Visitor

5 years ago

Solved

Adding data depending on 2 links : an ID and a date

Hello,

I'm having difficulties to define a solution to the following problem.

I'm trying to join 2 tables but using different data of the first table depending on a date from the second table.

Let me explain.

I have a list of sensors and their properties who can be moved from a location to another. The table is built on the following model :

Sensor ID	Location	Attribute	Installation date	Uninstallation date
ID#1	Location #1	5	01/01/2021	01/15/2021
ID#2	Location #2	7	01/01/2021
ID#1	Location #3	4	01/16/2021
ID#3	Location #4	5	01/01/2021

The second table is a table of measures built on the folowing model

Sensor ID	Date of measure	Measure
ID#1	01/03/2021	1.2
ID#2	01/05/2021	2.1
ID#1	01/14/2021	1.6
ID#1	01/17/2021	1.5
ID#3	01/19/2021	1.4
ID#1	01/24/2021	1.5
ID#2	01/27/2021	1.7

I would like to add the "Location" and the "Attribute" of the first table to the corresponding measure in the second table depending on the date of the measure.

If the "Uninstallation Date" in the first table is not completed, the sensor is supposed to be still installed at this location.

The result should look like this :

Sensor ID	Date of measure	Measure	Location	Attribute
ID#1	01/03/2021	1.2	Location #1	5
ID#2	01/05/2021	2.1	Location #2	7
ID#1	01/14/2021	1.6	Location #1	5
ID#1	01/17/2021	1.5	Location #3	4
ID#3	01/19/2021	1.4	Location #4	5
ID#1	01/24/2021	1.5	Location #3	4
ID#2	01/27/2021	1.7	Location #2	7

How can I achieve this in PowerBI ?

Thank You.

Charles

Need Help

v-henryk-mstf

5 years ago

Hi Charlesdudu ,

According to your needs, based on the date as the judgment condition, two measures are created to get the correct results. Refer to the following:

nlocation =
VAR _sensorid =
    SELECTEDVALUE ( 'Table2'[Sensor ID] )
VAR _curdate =
    SELECTEDVALUE ( 'Table2'[Date of measure] )
VAR _insdate =
    CALCULATE (
        MAX ( 'Table1'[Installation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _uinsdate =
    CALCULATE (
        MAX ( 'Table1'[Uninstallation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _insdate1 =
    CALCULATE (
        MAX ( 'Table1'[Installation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] = BLANK ()
        )
    )
VAR _location =
    CALCULATE (
        MAX ( 'Table1'[Location] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _location1 =
    CALCULATE (
        MAX ( 'Table1'[Location] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] = BLANK ()
        )
    )
RETURN
    IF ( _curdate >= _insdate && _curdate <= _uinsdate, _location, _location1 )

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

amitchandak
Super User
5 years ago
Charlesdudu , Create a new column in table 2

New column =
maxx(filter(Table1, table1[Sensor ID] = table2[Sensor ID] && table2[Date of measure] >= table1[Installation date] && (table2[Date of measure] <= table1[Uninstallation date]
|| isblank(table1[Uninstallation date]))), Table1[Location])
- Charlesdudu
  Regular Visitor
  5 years ago
  Hello,
  
  Thank you for your answer.
  
  I manage to compare the date of both tables using the formula "RELATED" and creating a relation n:n between Tabel 1 and Table 2 on the Field "Sensor ID".
  But I have an error : "Column 'Table1[Sensor ID]' doesn't exist or doesn't have relation with an existing table in the actual context"
  
  How should I overpass this problem ?
  
  Thank you

v-henryk-mstf

Community Support

5 years ago

Hi Charlesdudu ,

According to your needs, based on the date as the judgment condition, two measures are created to get the correct results. Refer to the following:

nlocation =
VAR _sensorid =
    SELECTEDVALUE ( 'Table2'[Sensor ID] )
VAR _curdate =
    SELECTEDVALUE ( 'Table2'[Date of measure] )
VAR _insdate =
    CALCULATE (
        MAX ( 'Table1'[Installation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _uinsdate =
    CALCULATE (
        MAX ( 'Table1'[Uninstallation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _insdate1 =
    CALCULATE (
        MAX ( 'Table1'[Installation date] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] = BLANK ()
        )
    )
VAR _location =
    CALCULATE (
        MAX ( 'Table1'[Location] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] <> BLANK ()
        )
    )
VAR _location1 =
    CALCULATE (
        MAX ( 'Table1'[Location] ),
        FILTER (
            'Table1',
            'Table1'[Sensor ID] = _sensorid
                && 'Table1'[Uninstallation date] = BLANK ()
        )
    )
RETURN
    IF ( _curdate >= _insdate && _curdate <= _uinsdate, _location, _location1 )

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Adding data depending on 2 links : an ID and a date

3 Replies

Related content

Circular dependency

Dax query dependency

dependent slicers

Adding columns

Dependent Filters and Filter Depend on Slicer