Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
romovaro
Responsive Resident
Responsive Resident

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
CountryCountry IDCapacity EEs Per EntityStartup 1-5Existing 1-5Existing 6-30Existing 31-100Existing 101-200Existing 201-500Existing 501-1000Fiscal Year DD/MM
AMERAntigua & BarbudaAG1003344NANA 01/01 to 31/12
AMERArgentinaAR1000444456701/01 to 31/12
AMERArubaAW100444445501/01 to 31/12
AMERBahamasBS20033445NANA01/01 to 31/12
AMERBermudaBM20022334NANA01/01 to 31/12
AMERBoliviaBO20033445NANA01/01 to 31/12
AMERBrazilBR1000444445501/01 to 31/12
AMERBritish Virgin IslandsVG10033445NANA01/01 to 31/12
AMERCanadaCA1000444555501/01 to 31/12
AMERTrinidad & TobagoTT1003344NANANA01/01 to 31/12
AMERUnited StatesUS1000444445701/01 to 31/12
AMERUruguayUY1000334445501/01 to 31/12
AMERVenezuelaVE1000444557701/01 to 31/12
EMEAFranceFR1000444555801/01 to 31/12
EMEAGeorgiaGE1000223456701-01 to 31-12
EMEAGermanyDE1000333334401/01 – 31/12
EMEAGhanaGH1000222334501/01 to 31/12
EMEAGreeceGR1000233456601/01 to 31/12
EMEAGuernseyGG25044447NANA 

 

 

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 File
EMEAFranceNew business18012/2/20244/1/20255
AMERUnited StatesNew business2612/1/20243/2/20254
EMEAGermanyNew business62511/1/20242/1/20254
AMERCanadaNew business1011/1/20243/2/20254
EMEACyprusNew business94/21/20257/1/20252
EMEAGermanyNew business196/20/202510/1/20253

 

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.

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

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? 

DataNinja777_0-1728112694006.png

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
)

DataNinja777_1-1728115371270.png

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,

 

View solution in original post

4 REPLIES 4
DataNinja777
Super User
Super User

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? 

DataNinja777_0-1728112694006.png

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
)

DataNinja777_1-1728115371270.png

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,

 

Yes, duplications are correct. We can have multiple projects in Germany. Thanks, your proposal works. thanks

Yes, duplications are correct. We can have multiple projects in Germany. Thanks, your proposal works. thanks
rajendraongole1
Super User
Super User

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.