topic Re: Match value in one table to row in another table in DAX Commands and Tips

Match value in one table to row in another table

Alicia_Anderson — Wed, 14 Aug 2024 19:03:00 GMT

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-jMtIiV4qXJF171ABawl2J2psPMFhFkgw0JdbUw?email=alicia.anderson%40alterahealth.com&e=YheLPr

Here is a picture of the relationhships.

Re: Match value in one table to row in another table

lbendlin — Wed, 14 Aug 2024 01:06:02 GMT

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-Forum/ba-p/963216

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/1447523

Re: Match value in one table to row in another table

Alicia_Anderson — Wed, 14 Aug 2024 19:03:54 GMT

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

Re: Match value in one table to row in another table

Alicia_Anderson — Wed, 14 Aug 2024 17:55:04 GMT

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.

Re: Match value in one table to row in another table

lbendlin — Wed, 14 Aug 2024 20:47:03 GMT

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.

Re: Match value in one table to row in another table

Irwan — Thu, 15 Aug 2024 01:53:28 GMT

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.

Re: Match value in one table to row in another table

Alicia_Anderson — Fri, 16 Aug 2024 16:56:35 GMT

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