Create new table from specific column/data from two different existing tables

[zahidah_mabd]

Hi, I want to create a new table to extract specific data that I have created in existing tables.

There are 2 tables:

[zhpla 2022] contains position info

[Flexi] contains employee info

Basically I have created several measures to check for any data issues (i.e: Incorrect title gender). I managed to create a separate table for each data issue rules.

Missing Cost Center = 

SELECTCOLUMNS(FILTER('zhpla 2022', 'zhpla 2022'[Cost Center] = BLANK() ),

"Data Issue", "Missing Cost Center",

"Position ID", 'zhpla 2022'[Position Code],

"Position Name", 'zhpla 2022'[Position Name],

"Staff ID", 'zhpla 2022'[Employee Number],

"Staff Name", 'zhpla 2022'[Employee Name],

"Job Name", 'zhpla 2022'[Job Name],

"Position Assignment Category", 'zhpla 2022'[Position Assignment Category],

"Business", 'zhpla 2022'[Business],

"Entities", 'zhpla 2022'[Entities],

"Dimension", "Completeness")

this one works.

However, the problem is when I want to create a new seperate table for the specific issue, I am unable to do so. For instance, in this case I need to extract certain data from both tables.

Incorrect Title/Gender = 
SELECTCOLUMNS(FILTER('Flexi', 'Flexi'[Title vs Gender] = "Invalid" ),


"Data Issue", "Incorrect Title/Gender",
"Position ID", 'zhpla 2022'[Position Code],
"Position Name", 'zhpla 2022'[Position Name],
"Staff ID", 'zhpla 2022'[Personnel Number],
"Staff Name", 'zhpla 2022'[Employee Name],
"Job Name", 'Flexi'[Job Name],
"Position Assignment Category", 'zhpla 2022'[Position Assignment Category],
"Business", 'zhpla 2022'[Business],
"Entities", 'zhpla 2022'[Entities],
"Dimension", "Consistency")

but I cant seem to pull the data is its from 2 different tables.

Can you help me?

[johnt75]

If the relationship between the tables is one-to-one or many-to-one then you can use RELATED. If it it is one-to-one then it doesn't matter which you use as the base for the SELECTCOLUMNS, if it is many to one then you would need to use the many side as the base, e.g.

New Table = SELECTCOLUMNS( 'Many side',
"col from many side", 'Many side'[col from many],
"col from one side", RELATED('One side'[col from one side])
)

If the relationship is many-to-many, or there is no relationship, you will have to use LOOKUPVALUE, e.g.

New Table = SELECTCOLUMNS( 'Table A',
"col from A", 'Table A'[col from A],
"col from B", LOOKUPVALUE('Table B'[col from B], 'Table B'[column to search], 'Table A'[value to look for])
)

[zahidah_mabd]

Hi, thanks for responding.
But im a bit confused with the lookup parameters here:

LOOKUPVALUE('Table B'[col from B], 'Table B'[column to search], 'Table A'[value to look for])

what the difference between [col from B] and [column to search]

"Job Name", LOOKUPVALUE('zhpla 2022'[Job Name], 'zhpla 2022'[Position Code], 'Flexi'[Title vs Gender] = "Invalid")

I tried this but it doesnt work. How do I select the value with specific filter?

Can you give an example on how to get the value with specific filter usin lookup?

[johnt75]

"Job Name", LOOKUPVALUE('zhpla 2022'[Job Name], 'Flexi'[Position Code], 'zhpla 2022'[Position Code], 'Flexi'[Title vs Gender], "Invalid")

[zahidah_mabd]

Still doesnt work.