Forum Discussion
Next lowest with multiple criteria
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
3 Replies
- foodd
Community Champion
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-Forum/ba-p/963216
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. 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
