Hello, community!
I`ve been facing an issue where I need to correlate 3 different tables and multiply a Count on the first table with the number of results expected.
Here`s the problem:
Table 1:
Table 2:
| SERIALNO | PRODUCTNO | SIDE |
| XXX1 | YYY1 | A |
| XXX1 | YYY1 | B |
| XXX2 | YYY1 | A |
| XXX3 | YYY2 | A |
Table 3:
| PRODUCTNO | SIDE | QTTSIDES |
| YYY1 | A | 2 |
| YYY1 | B | 2 |
| YYY2 | A | 1 |
| YYY3 | A | 2 |
| YYY3 | B | 2 |
Table3[QTTSIDES] = CALCULATE(
COUNTROWS('Table3'),
ALLEXCEPT('Table3','Table3'[PRODUCTNO])
)
I need to calculate, with those tables, the expected quantity (count) of a serial on table 2 if it appeared on table 1.
In this example, the result must be 5, because it expects that every serial from ProductNo 'YYY1' should appear 2 times on table 2 because of it's 2 sides, and every serial from ProductNo 'YYY2' should appear 1 time on table 2. So we can see that serial 'XXX2' is missing one side.
The point is calculating distinct Serials from table 1 * SideQtt from table 2 and comparing with the expected result, being able to view expected serials to be found on table 2 and the actually found.
