Forum Discussion
If column equals then use value from another table
I have two tables with similar levels of data, and I want to run an IF formula to determin which table to pull the value from.
The two tables have a many-many relationship on Intersection (Although in my example it could be a many-one).
I effectively want the following formula, but I know that the logicaltest wont work against a column value, so any help to point in the right direction.
IF MEASURE = IF ( OR (ABS[Group], Forecast[GROUP]) = "GP1", ABS[Value], Forecast[Value] )
Both ABS[Value] and Forecast[Value] have already been made into measures aswell.
Additionally, how to I make it so that the table has all the unique intersection values from both original tables.
Table 1 = Forecast
| Intersection | Group | Area | SKU | Submission | Date | Value |
| GP1 | AA1 | SKU01 | GP1 | AA1 | SKU01 | 01/01/2024 | 01/01/2024 | 1 |
| GP1 | AA1 | SKU02 | GP1 | AA1 | SKU02 | 01/01/2024 | 01/01/2024 | 2 |
| GP1 | AA2 | SKU02 | GP1 | AA2 | SKU02 | 01/01/2024 | 01/01/2024 | 5 |
| GP1 | AA2 | SKU03 | GP1 | AA2 | SKU03 | 01/01/2024 | 01/01/2024 | 6 |
| GP1 | AA3 | SKU01 | GP1 | AA3 | SKU01 | 01/01/2024 | 01/01/2024 | 7 |
| GP1 | AA3 | SKU03 | GP1 | AA3 | SKU03 | 01/01/2024 | 01/01/2024 | 9 |
| GP2 | AA1 | SKU01 | GP2 | AA1 | SKU01 | 01/01/2024 | 01/01/2024 | 10 |
| GP2 | AA1 | SKU02 | GP2 | AA1 | SKU02 | 01/01/2024 | 01/01/2024 | 11 |
| GP2 | AA1 | SKU03 | GP2 | AA1 | SKU03 | 01/01/2024 | 01/01/2024 | 12 |
| GP2 | AA2 | SKU01 | GP2 | AA2 | SKU01 | 01/01/2024 | 01/01/2024 | 13 |
| GP2 | AA2 | SKU02 | GP2 | AA2 | SKU02 | 01/01/2024 | 01/01/2024 | 14 |
| GP2 | AA2 | SKU03 | GP2 | AA2 | SKU03 | 01/01/2024 | 01/01/2024 | 15 |
| GP2 | AA3 | SKU01 | GP2 | AA3 | SKU01 | 01/01/2024 | 01/01/2024 | 16 |
| GP2 | AA3 | SKU02 | GP2 | AA3 | SKU02 | 01/01/2024 | 01/01/2024 | 17 |
| GP2 | AA3 | SKU03 | GP2 | AA3 | SKU03 | 01/01/2024 | 01/01/2024 | 18 |
| GP1 | AA1 | SKU01 | GP1 | AA1 | SKU01 | 01/12/2023 | 01/01/2024 | 51 |
| GP1 | AA1 | SKU02 | GP1 | AA1 | SKU02 | 01/12/2023 | 01/01/2024 | 52 |
| GP1 | AA2 | SKU02 | GP1 | AA2 | SKU02 | 01/12/2023 | 01/01/2024 | 55 |
| GP1 | AA2 | SKU03 | GP1 | AA2 | SKU03 | 01/12/2023 | 01/01/2024 | 56 |
| GP1 | AA3 | SKU01 | GP1 | AA3 | SKU01 | 01/12/2023 | 01/01/2024 | 57 |
| GP1 | AA3 | SKU03 | GP1 | AA3 | SKU03 | 01/12/2023 | 01/01/2024 | 59 |
| GP2 | AA1 | SKU01 | GP2 | AA1 | SKU01 | 01/12/2023 | 01/01/2024 | 60 |
| GP2 | AA1 | SKU02 | GP2 | AA1 | SKU02 | 01/12/2023 | 01/01/2024 | 61 |
| GP2 | AA1 | SKU03 | GP2 | AA1 | SKU03 | 01/12/2023 | 01/01/2024 | 62 |
| GP2 | AA2 | SKU01 | GP2 | AA2 | SKU01 | 01/12/2023 | 01/01/2024 | 63 |
| GP2 | AA2 | SKU02 | GP2 | AA2 | SKU02 | 01/12/2023 | 01/01/2024 | 64 |
| GP2 | AA2 | SKU03 | GP2 | AA2 | SKU03 | 01/12/2023 | 01/01/2024 | 65 |
| GP2 | AA3 | SKU01 | GP2 | AA3 | SKU01 | 01/12/2023 | 01/01/2024 | 66 |
| GP2 | AA3 | SKU02 | GP2 | AA3 | SKU02 | 01/12/2023 | 01/01/2024 | 67 |
| GP2 | AA3 | SKU03 | GP2 | AA3 | SKU03 | 01/12/2023 | 01/01/2024 | 68 |
Table 2 = ABP
| Intersection | Group | Area | SKU | Date | Value |
| GP1 | AA1 | SKU02 | GP1 | AA1 | SKU02 | 01/01/2024 | 102 |
| GP1 | AA1 | SKU03 | GP1 | AA1 | SKU03 | 01/01/2024 | 103 |
| GP1 | AA2 | SKU01 | GP1 | AA2 | SKU01 | 01/01/2024 | 104 |
| GP1 | AA2 | SKU02 | GP1 | AA2 | SKU02 | 01/01/2024 | 105 |
| GP1 | AA2 | SKU03 | GP1 | AA2 | SKU03 | 01/01/2024 | 106 |
| GP1 | AA3 | SKU01 | GP1 | AA3 | SKU01 | 01/01/2024 | 107 |
| GP1 | AA3 | SKU02 | GP1 | AA3 | SKU02 | 01/01/2024 | 108 |
| GP1 | AA3 | SKU04 | GP1 | AA3 | SKU04 | 01/01/2024 | 110 |
Expected Output Table
| Intersection | ABP | Value | IF ELSE |
| GP1 | AA1 | SKU01 | 1 | 0 | |
| GP1 | AA1 | SKU02 | 102 | 2 | 102 |
| GP1 | AA1 | SKU03 | 103 | 103 | |
| GP1 | AA2 | SKU01 | 104 | 104 | |
| GP1 | AA2 | SKU02 | 105 | 5 | 105 |
| GP1 | AA2 | SKU03 | 106 | 6 | 106 |
| GP1 | AA3 | SKU01 | 107 | 7 | 107 |
| GP1 | AA3 | SKU02 | 108 | 108 | |
| GP1 | AA3 | SKU03 | 9 | 0 | |
| GP1 | AA3 | SKU04 | 110 | 110 | |
| GP2 | AA1 | SKU01 | 10 | 10 | |
| GP2 | AA1 | SKU02 | 11 | 11 | |
| GP2 | AA1 | SKU03 | 12 | 12 | |
| GP2 | AA2 | SKU01 | 13 | 13 | |
| GP2 | AA2 | SKU02 | 14 | 14 | |
| GP2 | AA2 | SKU03 | 15 | 15 | |
| GP2 | AA3 | SKU01 | 16 | 16 | |
| GP2 | AA3 | SKU02 | 17 | 17 | |
| GP2 | AA3 | SKU03 | 18 | 18 |
Hi Anonymous,
I'd create an additional table with intersections instead of many-to-many relationship:
Then using the only column from the new table and the measure on the screenshot below you can achieve the result you expect:
Please check the attached file with this solution.
Best Regards,
Alexander
2 Replies
- barritown
Solution Sage
Hi Anonymous,
I'd create an additional table with intersections instead of many-to-many relationship:
Then using the only column from the new table and the measure on the screenshot below you can achieve the result you expect:
Please check the attached file with this solution.
Best Regards,
Alexander
- AnonymousNot applicable
Hi Anonymous ,
Based on the description, barritown provide reply should be helpful.
If you have solved the problem, please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Wisdom Wu