Match value in one table to row in another table

[Alicia_Anderson]

I have a Dimension table called Teams with [Team] value.  This is the main slicer on my page.

I have a Capacity table with three fields [Yr\Sprint], [Capacity], [Team].  (1>many relationship to above.) 

I created a measure to capture the Running Total for Capacity.  When viewing a table, the data looks correct.   

Cap_RT = CALCULATE(SUM('Capacity'[Capacity]),

    FILTER(ALLSELECTED(Capacity), 'Capacity'[Yr\Sprint] <= MAX('Capacity'[Yr\Sprint]) )  )

The main table on the page displays 'Features Open'.   There is a 1>many relationship on Team to Teams[Team] here as well.

I have a measure that calculates the Running Total of RemPts.   The data in this table also looks right.  

RemPts_RT = CALCULATE(SUM('Features Open'[RemPts]),

    FILTER(ALLSELECTED('Features Open'),

        'Features Open'[Backlog Priority] <= MAX('Features Open'[Backlog Priority]) &&

        'Features Open'[Team] = MAX('Features Open'[Team])  )  )

I want a measure that uses  the running total from the left table (Features Open) and finds the row with the closest running total (that is not greater) to the table on the right (Capacity).  If the Run Total from Features Open table is <= Run Total from Capacity table, then append the last 2 digits of the Yr\Sprint column to "Fits in S".  If not found, show "Beyond".

This is what my measure currently looks like, but it is not correct: 

Fits in =

VAR SelectedTeam = SELECTEDVALUE(Teams[Team])

VAR RemPtsRT = CALCULATE([RemPts_RT], 'Features Open'[Team] = SelectedTeam)

VAR CapRT = CALCULATE([Cap_RT], Capacity[Team] = SelectedTeam) RETURN

IF (RemPtsRT <= CapRT, "Fits in S"& RIGHT(CALCULATE(MAX(Capacity[Yr\Sprint])),2), "")

Here is a screenshot of the 2 tables with the Expected/Desired result in the first table.   

Here is a link to the PBIX: https://allscriptshealthcare-my.sharepoint.com/:u:/g/personal/alicia_anderson_allscripts_com/EZ9lWmr...

Here is a picture of the relationhships.

[Alicia_Anderson]

This is a table representation of the screenshot above...

Index	F_ID	F_State	Effort	ChdPtRem	RemPts	Run Total	Fits in	EXPECTED		RT left<=RT right (last 2 digits of Yr\Sprint)		Yr\Sprint	Cap	RT	Row#
1	8543747	Committed	21	2	2	2	Fits in S17	Fits in S17		2 <= 29 (row 1= 2024\17)		2024\17	29	29	1
2	8633585	Committed	34	13	13	15	Fits in S17	Fits in S17		15 <= 29 (row 1= 2024\17)		2024\18	29	58	2
3	8768829	Committed	55	17	17	32		Fits in S18		32 <= 58 (row 2= 2024\18)		2024\19	32	90	3
4	8697798	In Progress	21			32	Fits in S16	Fits in S18		32 <= 58 (row 2= 2024\18)		2024\20	32	122	4
5	8716443	Analyzing	21	13	21	53	Fits in S18	Fits in S18		53 <= 58 (row 2= 2024\18)		2024\21	31	153	5
6	8715146	Committed	18	5	5	58	Fits in S17	Fits in S18		58 <= 58 (row 2= 2024\18)		2024\22	32	185	6
7	8716210	Committed	34	9	9	67		Fits in S19		67 <= 90 (row 3= 2024\19)		2024\23	31	216	7
8	8716203	Committed	21	11	11	78	Fits in S18	Fits in S19		78 <= 90 (row 3= 2024\19)		2024\24	40	256	8
9	8669412	Committed	34	45	45	123	Fits in S27	Fits in S21		123 <= 153 (row 5= 2024\21)		2024\25	34	290	9
10	8710016	Committed	21	12	12	138	Fits in S20	Fits in S21		138 <= 153 (row 5= 2024\21)		2024\26	37	327	10
11	8773530	Committed	6	3	3	138		Fits in S21		138 <= 153 (row 5= 2024\21)		2024\27	36	363	11
12	8651185	Committed	34	80	80	218	Fits in S27	Fits in S21		218 <= 256 (row 8= 2024\24)		2025\01	40	403	12
13	8543757	Committed	13	6	6	224		Fits in S24		224 <=256 (row 8= 2024\24)		2025\02	40	443	13
14	8576248	Committed	21			224		Fits in S24		224 <=256 (row 8= 2024\24)		2025\03	40	483	14
15	8657004	Analyzing	21		21	245		Fits in S24		245 <=256 (row 8= 2024\24)		2025\04	40	523	15
16	7928541	Approved	21	20	21	266		Fits in S25		266 <=290 (row 9= 2024\25)		2025\05	40	563	16
17	8389242	Committed	55	30	30	296		Fits in S26		296 <=327 (row 10= 2024\26)		2025\06	40	603	17
18	8576235	Ready		35	35	331		Fits in S27		331 <=363 (row 11= 2024\27)		2025\07	40	643	18
19	8693189	New		15	15	346		Fits in S27		346 <=363 (row 11= 2024\27)		2025\08	40	683	19
20	8576222	Analyzing	8	8	8	354		Fits in S27		354 <=363 (row 11= 2024\27)		2025\09	40	723	20
21	8576234	Analyzing		19	19	373		Fits in S01		373 <=403 (row 12= 2025\01)		2025\10	40	763	21

[Irwan]

hello @Alicia_Anderson 

please check if this accomodate your need.

create new calculated column with following DAX

Expected =
var _Value = 
MINX(
    FILTER(
        'Capacity',
        'FeaturedOpen'[Run Total]<='Capacity'[RT]
    ),
    'Capacity'[Yr\Sprint]
)
var _RightText = RIGHT(_Value,2)
var _AddText = CONCATENATE("Fits in S",_RightText)
Return
_AddText

_Value is meant to find out the matching value.

_RightText is defining two right number on Yr\Sprin.

_AddText is for adding text "Fits..."

Hope this will help.

Thank you.

[Alicia_Anderson · Accepted solution]

Thanks for the assist.   This actually worked when I used it as a measure. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Alicia_Anderson]

I have read the suggested article and still can't figure out how to provide access to the PBIX.  I revised my initial post and also posted an extract of the 2 tables from Excel. 

[lbendlin]

Please simplify your sample data, remove anything not related to the issue. 
Please show the expected outcome based on the sample data you provided.

Note: Power BI is a reporting tool. It is not a resource planning tool.