Match Different columns

Hi romovaro ,

I was planning to use lookupvalue to retrieve the headcount number from the SlippageReport table to the CapacityFile, using the 'Country' field as the identifier key. However, the duplicate 'Country' entries in the SlippageReport table are preventing me from doing that. Should there be duplicates in this context? Is this duplication due to the fact that there are multiple legal entities in Germany? 

Due to this duplication, I took a different approach and brought the standard implied duration from the CapacityFile to the SlippageReport table by writing the following DAX formula.

StandardImplDuration = 

SWITCH(
    TRUE(),
    SlippageReport[headcount] <= 5,
        LOOKUPVALUE(
            CapacityFile[Existing 1-5],
            CapacityFile[Country], SlippageReport[Country]
        ),
    SlippageReport[headcount] <= 30,
        LOOKUPVALUE(
            CapacityFile[Existing 6-30],
            CapacityFile[Country], SlippageReport[Country]
        ),
    SlippageReport[headcount] <= 100,
        LOOKUPVALUE(
            CapacityFile[Existing 31-100],
            CapacityFile[Country], SlippageReport[Country]
        ),
    SlippageReport[headcount] <= 200,
        LOOKUPVALUE(
            CapacityFile[Existing 101-200],
            CapacityFile[Country], SlippageReport[Country]
        ),
    SlippageReport[headcount] <= 500,
        LOOKUPVALUE(
            CapacityFile[Existing 201-500],
            CapacityFile[Country], SlippageReport[Country]
        ),
    SlippageReport[headcount] <= 1000,
        LOOKUPVALUE(
            CapacityFile[Existing 501-1000],
            CapacityFile[Country], SlippageReport[Country]
        ),
    BLANK()  -- Returns BLANK if none of the conditions are met
)

Since Cyprus is not in the CapacityFile table, the row output for Cyprus remains blank instead of showing 2, but the other outputs are in line with your expectations.

I have attached an example pbix file for your reference.

Best regards,