Hi Pbix community I have 2 excels file and created a formula to match different columns to see how many months we need to reduce fromGo live date to start working on the project. One file is called Capacity FileCapacity FileRegionCountryCountry IDCapacity EEs Per EntityStartup 1-5Existing 1-5Existing 6-30Existing 31-100Existing 101-200Existing 201-500Existing 501-1000Fiscal Year DD/MMAMERAntigua & BarbudaAG1003344NANA 01/01 to 31/12AMERArgentinaAR1000444456701/01 to 31/12AMERArubaAW100444445501/01 to 31/12AMERBahamasBS20033445NANA01/01 to 31/12AMERBermudaBM20022334NANA01/01 to 31/12AMERBoliviaBO20033445NANA01/01 to 31/12AMERBrazilBR1000444445501/01 to 31/12AMERBritish Virgin IslandsVG10033445NANA01/01 to 31/12AMERCanadaCA1000444555501/01 to 31/12AMERTrinidad & TobagoTT1003344NANANA01/01 to 31/12AMERUnited StatesUS1000444445701/01 to 31/12AMERUruguayUY1000334445501/01 to 31/12AMERVenezuelaVE1000444557701/01 to 31/12EMEAFranceFR1000444555801/01 to 31/12EMEAGeorgiaGE1000223456701-01 to 31-12EMEAGermanyDE1000333334401/01 – 31/12EMEAGhanaGH1000222334501/01 to 31/12EMEAGreeceGR1000233456601/01 to 31/12EMEAGuernseyGG25044447NANA And the other one is the Slippage Report (last column is the number from the Capacity File that I use to reduce from column Actual/Schedule Go Live Date) The formula I use in Excel is the one below: =INDEX('Capacity Book'!$C$2:$Q$203,MATCH(G31,'Capacity Book'!$C$2:$C$203,0),HLOOKUP(VLOOKUP(I31,'Capacity Book'!V:W,2,0),'Capacity Book'!$C$2:$M$203,2,0)) but having issues with PowerBI Work RegionCountryProject typeheadcountExpected start date of the work effort (YYYY-MM-DD)Actual/Schedule Go Live date (YYYY-MM-DD)Standard impl duration according to Capacity FileEMEAFranceNew business18012/2/20244/1/20255AMERUnited StatesNew business2612/1/20243/2/20254EMEAGermanyNew business62511/1/20242/1/20254AMERCanadaNew business1011/1/20243/2/20254EMEACyprusNew business94/21/20257/1/20252EMEAGermanyNew business196/20/202510/1/20253 For example... in the Slippage Report first row Is Region= EMEACountry=FranceHeadcount 180Go live Date = 04/01/2025 (MM-DD-YYYY) In the Capacity File For EMEA, France and Column "Existing 101-200" is 5. Meaning we need to start 5 months before the Go Live Date (December 2024)WE use "Existing 101-200" becasue the Headcount for this project is 180.

Forum Discussion

romovaro

Responsive Resident

1 year ago

Solved

Match Different columns

Hi Pbix community

I have 2 excels file and created a formula to match different columns to see how many months we need to reduce from

Go live date to start working on the project.

One file is called Capacity File

Capacity File
Region	Country	Country ID	Capacity EEs Per Entity	Startup 1-5	Existing 1-5	Existing 6-30	Existing 31-100	Existing 101-200	Existing 201-500	Existing 501-1000	Fiscal Year DD/MM
AMER	Antigua & Barbuda	AG	100	3	3	4	4	NA	NA		01/01 to 31/12
AMER	Argentina	AR	1000	4	4	4	4	5	6	7	01/01 to 31/12
AMER	Aruba	AW	100	4	4	4	4	4	5	5	01/01 to 31/12
AMER	Bahamas	BS	200	3	3	4	4	5	NA	NA	01/01 to 31/12
AMER	Bermuda	BM	200	2	2	3	3	4	NA	NA	01/01 to 31/12
AMER	Bolivia	BO	200	3	3	4	4	5	NA	NA	01/01 to 31/12
AMER	Brazil	BR	1000	4	4	4	4	4	5	5	01/01 to 31/12
AMER	British Virgin Islands	VG	100	3	3	4	4	5	NA	NA	01/01 to 31/12
AMER	Canada	CA	1000	4	4	4	5	5	5	5	01/01 to 31/12
AMER	Trinidad & Tobago	TT	100	3	3	4	4	NA	NA	NA	01/01 to 31/12
AMER	United States	US	1000	4	4	4	4	4	5	7	01/01 to 31/12
AMER	Uruguay	UY	1000	3	3	4	4	4	5	5	01/01 to 31/12
AMER	Venezuela	VE	1000	4	4	4	5	5	7	7	01/01 to 31/12
EMEA	France	FR	1000	4	4	4	5	5	5	8	01/01 to 31/12
EMEA	Georgia	GE	1000	2	2	3	4	5	6	7	01-01 to 31-12
EMEA	Germany	DE	1000	3	3	3	3	3	4	4	01/01 – 31/12
EMEA	Ghana	GH	1000	2	2	2	3	3	4	5	01/01 to 31/12
EMEA	Greece	GR	1000	2	3	3	4	5	6	6	01/01 to 31/12
EMEA	Guernsey	GG	250	4	4	4	4	7	NA	NA

And the other one is the Slippage Report (last column is the number from the Capacity File that I use to reduce from column Actual/Schedule Go Live Date)

The formula I use in Excel is the one below:

=INDEX('Capacity Book'!$C$2:$Q$203,MATCH(G31,'Capacity Book'!$C$2:$C$203,0),HLOOKUP(VLOOKUP(I31,'Capacity Book'!V:W,2,0),'Capacity Book'!$C$2:$M$203,2,0))

but having issues with PowerBI

Work Region	Country	Project type	headcount	Expected start date of the work effort (YYYY-MM-DD)	Actual/Schedule Go Live date (YYYY-MM-DD)	Standard impl duration according to Capacity File
EMEA	France	New business	180	12/2/2024	4/1/2025	5
AMER	United States	New business	26	12/1/2024	3/2/2025	4
EMEA	Germany	New business	625	11/1/2024	2/1/2025	4
AMER	Canada	New business	10	11/1/2024	3/2/2025	4
EMEA	Cyprus	New business	9	4/21/2025	7/1/2025	2
EMEA	Germany	New business	19	6/20/2025	10/1/2025	3

For example...

in the Slippage Report first row Is

Region= EMEA

Country=France

Headcount 180

Go live Date = 04/01/2025 (MM-DD-YYYY)

In the Capacity File For EMEA, France and Column "Existing 101-200" is 5. Meaning we need to start 5 months before the Go Live Date (December 2024)

WE use "Existing 101-200" becasue the Headcount for this project is 180.

Need Help

DataNinja777

1 year ago

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,

Match Different columns.pbix40 KB

4 Replies

rajendraongole1
Super User
1 year ago
Hi romovaro - hope you already have a relationship between the Region and Country columns from both tables.

You will use DAX to mimic your Excel formula in Power BI. create a measure as below:

Months_to_Subtract =
VAR RegionVal = 'SlippageReport'[Work Region]
VAR CountryVal = 'SlippageReport'[Country]
VAR Headcount = 'SlippageReport'[headcount]
RETURN
CALCULATE(
SWITCH(
TRUE(),
Headcount <= 5, LOOKUPVALUE('CapacityFile'[Startup 1-5], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 30, LOOKUPVALUE('CapacityFile'[Existing 1-5], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 100, LOOKUPVALUE('CapacityFile'[Existing 6-30], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 200, LOOKUPVALUE('CapacityFile'[Existing 31-100], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 500, LOOKUPVALUE('CapacityFile'[Existing 101-200], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 1000, LOOKUPVALUE('CapacityFile'[Existing 201-500], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount > 1000, LOOKUPVALUE('CapacityFile'[Existing 501-1000], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
BLANK()
)
)

Now, create another calculated column in the SlippageReport that adjusts the Go Live Date based on the slippage months retrieved in the previous step.

Adjusted_Start_Date =
'SlippageReport'[Actual/Schedule Go Live date] -
( [Months_to_Subtract] * 30 ) -- Assuming an average of 30 days per month

hope this helps in your calculation to find the capacity.
DataNinja777
Super User
1 year ago
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,

Match Different columns.pbix40 KB
- romovaro
  Responsive Resident
  1 year ago
  Yes, duplications are correct. We can have multiple projects in Germany. Thanks, your proposal works. thanks
- romovaro
  Responsive Resident
  1 year ago
  Yes, duplications are correct. We can have multiple projects in Germany. Thanks, your proposal works. thanks

Forum Discussion

Match Different columns

4 Replies

Related content

Matching Column Values

1. How to add two different calculated columns of two different table

Different schema into different destination

How to add two different calculated columns of two different table

Matching Different Column Names in Power Query