Next lowest with multiple criteria

[Anonymous]

I have a fixed table (Table 1) with columns for ID, X and Y values. Each ID may have 20 X and Y values. I have a second table (Table 2) with a Column ID and a measured value. I'm trying to find the X value from table 1 that is the next lowest value based on the ID and measured value provided in Table 2. Once I find the X value I will need to find the Y value but I need to report them as separate measures.

Table 1                                       Table 2: Measure Value= 11 for ID 1            My result should be 10 for X and 20 for Y 

ID    X     Y

1     10   20

1      15   25

2      10   30

Thank you

[Anonymous]

wont upload, wont let me copy as a table from excell.

[Anonymous]

Table 1

IPN	X_VALUE	Vol (AF)
1089	101	5167.63
1089	103	5465.07
1089	105	5775.73
1089	107	6099.9
1089	109	6436.99
1089	111	6786.57
1089	113	7148.94
1089	115	7524.05
1089	117	7912.33
1089	119	8313.83
1089	121	8728.72
1089	123	9157.32
1089	125	9599.76
1089	127	10056.23
1089	129	10526.88
1089	131	11012.04
1089	133	11513.25
1089	135	12030.76
1089	135.01	12033.35

Table 2: I need a measurement that looks at IPN 1089 and Level 127.33 and finds the next lowest number from table 1 using the IPN and the Level as the filter. It should be 127 as a result since  it is less then 127.33 and has an IPN of 1089. This should be done for each of the IPN numbers.

Line	IPN	Date	Level
953	1089	5/1/2023 0:00	127.33
954	1091	5/1/2023 0:00	32.58
955	1093	5/1/2023 0:00	29.62
956	1123	5/1/2023 0:00	171.63
957	1125	5/1/2023 0:00	34.31
958	1127	5/1/2023 0:00	10.91
959	3947	5/1/2023 0:00	0
960	1138	5/1/2023 0:00	15.3
961	1140	5/1/2023 0:00	14.77
962	1142	5/1/2023 0:00	30.98
963	1144	5/1/2023 0:00	32.67
964	1146	5/1/2023 0:00	17.01
965	1164	5/1/2023 0:00	16.99
966	1166	5/1/2023 0:00	30.67
967	1258	5/1/2023 0:00	74.02
968	1276	5/1/2023 0:00	30.36
969	1278	5/1/2023 0:00	60.22

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

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.

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

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.