I am in need of some help with this problem:
I have two tables, one for Ok orders, and another for not Ok orders:
Table1 (NOK orders):
Table 2 (OK orders):
In table 1(NOK orders), an IC Client could have more than one order. In table 2 (Ok orders), there is only one order per IC Client.
The thing is, I want to "delete" or not count the orders of an IC Client when the IC Client is both in Table 1 and Table 2; i.e. when a Client has an OK order, the NOK orders get annulled and don't count.
Thank you very much in advance.
Solved! Go to Solution.
You can use use the EXCEPT function, removing the clients from table2 if they are in table1.
After that u can filter the table and count the orders.
I would expect a table like this:
With IC client and its orders that only appears in table NOK.
Here are the Steps to achieve this
1. Create a relationship between the two tables OK and Non Ok
2. Create a calculated column in the Non Ok table. Lets call it Dummy with the following formula =IF(ISBLANK(COUNTROWS(RELATEDTABLE(ok))),1,BLANK())
3. Now in the create a table with IC Client and Order and against that add that dummy column. All these 3 from the Non Ok table
Here is the result. You can download the Power Pivot workbook from here