Circular Dependency when calculating Max date column which is a lookup value from another table

[Anonymous]

Hi!

I am getting a circular dependency error when do a earliest date column based on the Date colimn whihc i brought as a Lookup value from another tab;e. Really appreciate if someone has encountered such issue and have a solution.

Table 1                                        Table 2

ID        Date                               Tag         ID          Date(Vlookup from Table 1)     Earliest Date

1        2020-03-02                      A            1           2020-03-02                             Should be - 2020-01-01

2        2019-07-6                        A            3           2020-01-01                             Should Be - 2020-01-01

3        2020-01-01  

Following is my logic:

Earliest Date = CALCULATE (

    MINX (

         SUMMARIZE (

            'Table 2',

            'Table 2'[Tag ID],

            "minimumdate", min('Table 2'[Date])

        ),

        [minimumdate]

    )

)

Thanks

Now I want to create a new column for the Earliest Date in Table 2 Based on Tag ID.

When I try doing so I get a circular dependency error                  

[parry2k]

@Anonymous in your example dataset there is no column called Tag ID, can you please edit your post and clarify it.

[Anonymous]

Hi Parry2K!

This is what I am expecting to show in the Earliest Date column:Example

Tag ID             ID            Date(This is a Lookup value from Table 1)       Earliest Date Based on TagID(Expected Result)
IPP-18-17       abc123            Blank                                                             2020-10-15
IPP-18-17       abc123       2020-10-31                                                   2020-10-15
IPP-18-17       xyz123        2020-10-25                                                   2020-10-15
IPP-18-17       def345       2020-10-15                                                   2020-10-15
IPP-18-17       abc123      2020-10-17                                                   2020-10-15
IPP-18-17       abc123      2020-11-16                                                   2020-10-15
IPP-18-17      def345        2020-11-12                                                  2020-10-15
IPP-18-17      xyz123        2020-11-09                                                  2020-10-15
IPP-18-17      abc123        2020-11-27                                                  2020-10-15

[parry2k]

@Anonymous so I guess you have one to many relationships from table 1, correct? You can use relation function to get the date from table1 instead of using lookupvalue function.

[parry2k · Accepted solution]

@Anonymous here is the dax expression to add new column to get the earliest date

Earliest Date = CALCULATE ( MIN ( TableDate[Date] ), ALLEXCEPT ( TableDate, TableDate[Tag Id] ) ) 

Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

Sorry about that , the Tag column on the Table 2 is the Tag ID.

Table 1                                        Table 2

ID        Date                               Tag ID         ID          Date(Vlookup from Table 1)     Earliest Date

1        2020-03-02                      A            1           2020-03-02                             Should be - 2020-01-01

2        2019-07-6                        A            3           2020-01-01                             Should Be - 2020-01-01

3        2020-01-01