cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate II
Advocate II

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

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","")

View solution in original post

1 REPLY 1
Highlighted
Advocate II
Advocate II

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

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","")

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (878)