Circular Dependency from a UNION table to a calendar file...

[zhivaninz]

I have the following relevant tables

AMS (system1)

ADEPT (system2)

CALENDAR

Currently, both AMS and ADEPT are linked to CALENDAR, and both calculate a processing time IN TABLE (calculated column) by calculating the difference between two dates and then filtering out dates in CALENDAR marked as "non working days."

I have created a UNION table to combine both AMS and ADEPT to get overall timeliness.

DATA_COMBINED_AMS_ADEPT = 
UNION(
    SELECTCOLUMNS(
        FILTER(
            ADEPT_PROCESSING, 
            NOT ISBLANK(ADEPT_PROCESSING[Decisiondate])
        ),
        "Working Days", ADEPT_PROCESSING[Work Days Tendered to Decided],
        "ID", ADEPT_PROCESSING[inz_visaapplicationid],
        "Decided Date", ADEPT_PROCESSING[Decisiondate],
        "Applicant Type", ADEPT_PROCESSING[applicanttype],
        "Decision Code", ADEPT_PROCESSING[Decided Boolean]
        ),
        SELECTCOLUMNS(
            FILTER(
                AMS_PROCESSING,
                NOT ISBLANK(AMS_PROCESSING[decision_date])
            ),
            "Working Days", AMS_PROCESSING[Work Days Tendered to Decided],
            "ID", AMS_PROCESSING[Application_number],
            "Decided Date", AMS_PROCESSING[decision_date],
            "Applicant Type", AMS_PROCESSING[applicant_type],
            "Decision Code", AMS_PROCESSING[DecisionLookup]
        )
)

This generates the right table, which is all completed applications in both systems, in a single table.

However, when I try to link this new table to CALENDAR:

I have tried whiteboarding this out, but cannot work it out. The UNION table is a union from two tables that happily link to CALENDAR.
Work Days Tendered to Decided does rely on Decided Date, but Decided Date does not rely on Work Days Tendered to Decided.

Before I kill the calculated columns and redo my calculations in measures (which I don't want to), was hoping someone might have an idea.

In Summary: 
* ADEPT -> CALENDAR

* AMS -> CALENDAR

* UNION = ADEPT+AMS

* Calculated columns in both ADEPT and AMS use CALENDAR for their working day calculation

[Ashish_Mathur]

Hi,

If the structure of both tables is the same, then append those tables in the Query Editor.

[zhivaninz]

Hi Ashish,

The tables are completely different, which is why I used the UNION (SELECTCOLUMNS) to be able to append the key columns (renamed). 
If there is some M code that does the same thing (i.e. replicates my DAX)?

[Ashish_Mathur]

If you ensure that the columns which you want to get into a single table are spelled the same way then you can use the Append feature of the Query Editor.

[Ashish_Mathur]

Hi,

If the structure of both tables is the same, then append those tables in the Query Editor.