Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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