cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WGAP75
Frequent Visitor

Lookup values between tables using hierarchical values

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:

JobIdGeoIdEmpReq_IdRoleCPTElemCPTLvlCPTKey
J.898P-CGR8967Maint LeadMec3Mec-3
J.898P-CGR8967Maint LeadHyd3Hyd-3
J.898P-CHF8762Prod CrewMfg2Mfg-2
J.898P-CHF8762Prod CrewPrd2Prd-2
J.898P-CHF8762Prod CrewMec2Mec-2
J.724P-DTC7846SupervisorMec4Mec-4
J.724P-DTC7846SupervisorEle4Ele-4
J.724P-DTC7846SupervisorHyd4Hyd-4
J.724P-DTC7846SupervisorPrd4Prd-4
J.724P-DTC7846SupervisorMfg4Mfg-4
J.724P-DWK8751Maint CrewMec2Mec-2
J.724P-DWK8751Maint CrewHyd2Hyd-2
J.653P-APT9985Prod LeadPrd3Prd-3
J.653P-APT9985Prod LeadMfg3Mfg-3
J.653P-APT9985Prod LeadHyd3Hyd-3
J.653P-ASR3476Maint CrewEle2Ele-2
J.653P-ASR3476Maint CrewHyd2Hyd-2
J.653P-AWA0475Prod CrewMfg3Mfg-3
J.653P-AWA0475Prod CrewMec2Mec-2

 

Table_Cap:

EmpIdGeoIdReqCPTElemCPTLvlCPTKey
12P-AMec4Mec-4
12P-AEle3Ele-3
12P-AHyd4Hyd-4
12P-APrd4Prd-4
12P-AMfg4Mfg-4
13P-CMec3Mec-3
13P-CEle3Ele-3
13P-CHyd2Hyd-2
13P-CMfg4Mfg-4
14P-BMec2Mec-2
14P-BEle2Ele-2
14P-BHyd3Hyd-3
14P-BPrd2Prd-2
14P-BMfg2Mfg-2
15P-AEle2Ele-2
15P-AHyd2Hyd-2
15P-APrd2Prd-2
15P-AMfg2Mfg-2
16P-CMec3Mec-3
16P-CEle3Ele-3
16P-CHyd3Hyd-3
17P-DMec4Mec-4
17P-DEle4Ele-4
17P-DHyd4Hyd-4
17P-DPrd4Prd-4
17P-DMfg4Mfg-4
18P-CMec2Mec-2
18P-CEle2Ele-2
18P-CHyd1Hyd-1
18P-CPrd1Prd-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_IdIn GeoOut_Geo
GR89671612
  17
HF8762 12
  14
  17
TC784617 
WK87511712
  13
  14
  16
PT99851217
SR34761213
 1514
  16
  17
WA04751213
  17

 

Desired Output as follow:

Req_IdIn GeoOut_Geo
GR896712
HF876203
TC784610
WK875114
PT998511
SR347624
WA047512
Distinct Count (EmpId)45

 

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

 

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @WGAP75 

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

Capture1.JPG

What does this mean:

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

 

Best Regards

Maggie

@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

  

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors