Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, community,
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):
IC Client | Order |
1000002806 | 13833842 |
1000002806 | 13864396 |
1000003077 | 13759224 |
1000004392 | 13722082 |
1000004392 | 13855291 |
1000004392 | 13862829 |
1000005625 | 13822518 |
1000007291 | 13727345 |
1000007291 | 13728120 |
1000007291 | 13733794 |
1000007291 | 13734227 |
Table 2 (OK orders):
IC Client | Order |
1000002806 | 13833842 |
1000007291 | 13734227 |
1000052711 | 13602943 |
1000032504 | 13669347 |
1000447084 | 13670026 |
1000535148 | 13673502 |
1000810433 | 13673563 |
1000693822 | 13673964 |
1001090437 | 13674447 |
1001094291 | 13688102 |
1001090038 | 13698426 |
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.
Hi @ALEX13
Create a relationship betweeen above tables using IC Client
Then we can use this calculated Table
Table = FILTER ( NOK_orders, ISBLANK ( RELATED ( OK_orders[Order] ) ) )
Hi @ALEX13
Create a relationship betweeen above tables using IC Client
Then we can use this calculated Table
Table = FILTER ( NOK_orders, ISBLANK ( RELATED ( OK_orders[Order] ) ) )
Hi @ALEX13,
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.
https://msdn.microsoft.com/en-us/library/mt243784.aspx
Ricardo
Hi Ricardo,
How can I filter the table? take into consideration that there may be more than one order per client.
Thanks.
Hi,
I would expect a table like this:
IC Client | Order |
1000003077 | 13759224 |
1000004392 | 13722082 |
1000004392 | 13855291 |
1000004392 | 13862829 |
1000005625 | 13822518 |
With IC client and its orders that only appears in table NOK.
Hi @ALEX13,
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