How to compare columns in a query (table) and a reference

[mz180w]

I have a Headcount query (table) that contains a monthly snapshot of Employees.

The Headcount query (HDCT) has the following columns:

 - HDCT Employee ID (ex. 6218)
 - HDCT As of Date (ex. 06/30/2017)
 - HDCT Salary Grade (ex. 6G)
 - HDCT Current Record ID (ex. 6218_2017_06)
 - HDCT Previous Record ID (ex. 6218_2016_05)

I created a Reference for the HDCT query (table) called HDCT_REF.
I have re-labeled the columns within HDCT_REF to be:
 - HDCT_REF Employee ID
 - HDCT_REF As of Date
 - HDCT_REF Salary Grade
 - HDCT_REF Current Record ID
 - HDCT_REF Previous Record ID

I have created a relationship between HDCT and HDCT_REF using the following definition:

 HDCT Previous Record ID = HDCT_REF Current Period ID
 The cardinality is 1:1; the cross filter directions is Both

This relationship provides the means to view a monthly snapshot record and the associated previous monthly snapshot record on the same row.
As example, the HDCT 06/2017 row is joined to the HDCT_REF 05/2017 record using HDCT Previous Record ID (6218_2017_05) to HDCT_REF Current Record ID (6218_2017_05)
When creating a visualization using a Table, this data appears as expected.

I need to compare the HDCT Salary Grade value with the HDCT_REF Salary Grade value to identify any differences.

How can this be accomplished with a Calculation?

[mz180w · Accepted solution]

The solution is using the RELATED function in the creation of a new calculated column within the primary fact table (query) that references a column (field) from the related table (query).

In this instance, within the HDCT query a new calculated column was created:

 HDCT Salary_Grade_Prev = RELATED(HDCT_REF[HDCT_REF(Salary Grade])

Because of the defined relationship between HDCT and HDCT_REF, HDCT Salary_Grade_Prev is able to reference the HDCT_REF Salary Grade value associated with the row from HDCT_REF.

With HDCT Salary_Grade_Prev defined, other new calculated columns can be created that reference it.  For example,

 HDCT Salary_Grade_Compare = IF(HDCT[HDCT Salary Grade]<>HDCT[HDCT Salary_Grade_Prev],"Salary Grade Change","")