Lookup values between tables using hierarchical values

[Anonymous]

Hello PBI Community;

I need your valuable help to create 2 measure to calculate the number of employees matching criteria from the requirements, these measures are based on 2 tables connected using a look up table, I'd like to describe the cases using the mock-up sample as follow:

Lookup Table:

CPTKey | CPTElem | CPTLvl

Table_Req:

JobId	GeoIdEmp	Req_Id	Role	CPTElem	CPTLvl	CPTKey
J.898	P-C	GR8967	Maint Lead	Mec	3	Mec-3
J.898	P-C	GR8967	Maint Lead	Hyd	3	Hyd-3
J.898	P-C	HF8762	Prod Crew	Mfg	2	Mfg-2
J.898	P-C	HF8762	Prod Crew	Prd	2	Prd-2
J.898	P-C	HF8762	Prod Crew	Mec	2	Mec-2
J.724	P-D	TC7846	Supervisor	Mec	4	Mec-4
J.724	P-D	TC7846	Supervisor	Ele	4	Ele-4
J.724	P-D	TC7846	Supervisor	Hyd	4	Hyd-4
J.724	P-D	TC7846	Supervisor	Prd	4	Prd-4
J.724	P-D	TC7846	Supervisor	Mfg	4	Mfg-4
J.724	P-D	WK8751	Maint Crew	Mec	2	Mec-2
J.724	P-D	WK8751	Maint Crew	Hyd	2	Hyd-2
J.653	P-A	PT9985	Prod Lead	Prd	3	Prd-3
J.653	P-A	PT9985	Prod Lead	Mfg	3	Mfg-3
J.653	P-A	PT9985	Prod Lead	Hyd	3	Hyd-3
J.653	P-A	SR3476	Maint Crew	Ele	2	Ele-2
J.653	P-A	SR3476	Maint Crew	Hyd	2	Hyd-2
J.653	P-A	WA0475	Prod Crew	Mfg	3	Mfg-3
J.653	P-A	WA0475	Prod Crew	Mec	2	Mec-2

Table_Cap:

EmpId	GeoIdReq	CPTElem	CPTLvl	CPTKey
12	P-A	Mec	4	Mec-4
12	P-A	Ele	3	Ele-3
12	P-A	Hyd	4	Hyd-4
12	P-A	Prd	4	Prd-4
12	P-A	Mfg	4	Mfg-4
13	P-C	Mec	3	Mec-3
13	P-C	Ele	3	Ele-3
13	P-C	Hyd	2	Hyd-2
13	P-C	Mfg	4	Mfg-4
14	P-B	Mec	2	Mec-2
14	P-B	Ele	2	Ele-2
14	P-B	Hyd	3	Hyd-3
14	P-B	Prd	2	Prd-2
14	P-B	Mfg	2	Mfg-2
15	P-A	Ele	2	Ele-2
15	P-A	Hyd	2	Hyd-2
15	P-A	Prd	2	Prd-2
15	P-A	Mfg	2	Mfg-2
16	P-C	Mec	3	Mec-3
16	P-C	Ele	3	Ele-3
16	P-C	Hyd	3	Hyd-3
17	P-D	Mec	4	Mec-4
17	P-D	Ele	4	Ele-4
17	P-D	Hyd	4	Hyd-4
17	P-D	Prd	4	Prd-4
17	P-D	Mfg	4	Mfg-4
18	P-C	Mec	2	Mec-2
18	P-C	Ele	2	Ele-2
18	P-C	Hyd	1	Hyd-1
18	P-C	Prd	1	Prd-1

The required measures should be able to find the EmpId's on Table_Cap matching the CPTElem + CPTLvl (Required or Above)

Measure 1 (InGeo) -> Matches having GeoIdEmp = GeoEmpReq

Measure 2 (OutGeo -> Matches having GeoIdEmp <> GeoEmpReq

Required Iteration logic to get match is as follow showing EmpId's:

Req_Id	In Geo	Out_Geo
GR8967	16	12
		17
HF8762		12
		14
		17
TC7846	17
WK8751	17	12
		13
		14
		16
PT9985	12	17
SR3476	12	13
	15	14
		16
		17
WA0475	12	13
		17

Desired Output as follow:

Req_Id	In Geo	Out_Geo
GR8967	1	2
HF8762	0	3
TC7846	1	0
WK8751	1	4
PT9985	1	1
SR3476	2	4
WA0475	1	2
Distinct Count (EmpId)	4	5

I managed to create a measure counting the Exact matches but doesn't consider the cases where the Table_Cap[CPTLvl] are Higher than Table_Req[CPTLvl]

I hope I provided all information you'll require to provide me the support.

Thanks in Advace;

WGAP75

[v-juanli-msft]

Hi @Anonymous 

As tested, for [Req_id]=GR8967, the corresponding id for [In Geo] has two :13,16, why the final out put is only 16.

What does this mean:

the cases where the Table_Cap[CPTLvl] are Higher than Table_Req[CPTLvl

Best Regards

Maggie

[Anonymous]

@v-juanli-msft ;

Thanks for your reply and support !!!

Regarding the value displayed value Req_Id GR1967, only 16 is showed in the "Manually created Iteration" results since CPTLvl is a key parameter in the calculation, the idea is to get the EmpId's matching the same CPTElem's +Table_Cap[CPTLvl] => Table_Cap[CPTLvl].  In this case EmpId "13" has CPTElem Mec = 3 but Hyd = 2 and Hyd = 3 from Table_Req, this is what I meant with "cases where the Table_Cap[CPTLvl] are Higher than Table_Req[CPTLvl]" 

 My previous approach was as follow but IS NOT considering the condition where Table_Cap[CPTLvl] >= Table_Req[CPTLvl]:

1) I created the measure:

EmpCheck = IF(CALCULATE(COUNTROWS(Table_Req), FILTER(Table_Req, Table_Req[CPTKey] in DISTINCT(Table_Cap[CPTKey]))) = COUNTROWS(Table_Cap), 1, 0)

2) Create a additional measures to get  In Geo and Out Geo

InGeoXP = IF(SELECTEDVALUE(Table_Cap[GeoId]) = SELECTEDVALUE(Table_Req[GeoId]), 1, 0)

In Geo = CALCULATE(DISTINCTCOUNT(Table_Cap[EmpId]), FILTER(Table_Cap, [InGeoXP] = 1))

Out Geo = DISTINCTCOUNT(Table_Cap[EmpId]) - [In Geo]

Probably I'm not far from the final solution (pherhaps yes ???) but I've been stucked for a while.

Hope it clarifies your questions.

Best Regards;

WGAP75