cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ALEX13 Regular Visitor
Regular Visitor

Delete or not count values from a table when the value is in another table

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 ClientOrder
100000280613833842
100000280613864396
100000307713759224
100000439213722082
100000439213855291
100000439213862829
100000562513822518
100000729113727345
100000729113728120
100000729113733794
100000729113734227

 

Table 2 (OK orders):

IC ClientOrder
100000280613833842
100000729113734227
100005271113602943
100003250413669347
100044708413670026
100053514813673502
100081043313673563
100069382213673964
100109043713674447
100109429113688102
100109003813698426

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Delete or not count values from a table when the value is in another table

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] ) ) )
8 REPLIES 8
ricardocamargos Established Member
Established Member

Re: Delete or not count values from a table when the value is in another table

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

ALEX13 Regular Visitor
Regular Visitor

Re: Delete or not count values from a table when the value is in another table

Hi Ricardo,

How can I filter the table? take into consideration that there may be more than one order per client.

Thanks.

Super User
Super User

Re: Delete or not count values from a table when the value is in another table

Hi Alex

What is your expected result above?
ChandeepChhabra Established Member
Established Member

Re: Delete or not count values from a table when the value is in another table

Hi @ALEX13

 

How do you wish to see the result?

ALEX13 Regular Visitor
Regular Visitor

Re: Delete or not count values from a table when the value is in another table

Hi,

I would expect a table like this:

 

IC ClientOrder
100000307713759224
100000439213722082
100000439213855291
100000439213862829
100000562513822518

 

With IC client and its orders that only appears in table NOK.

Super User
Super User

Re: Delete or not count values from a table when the value is in another table

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] ) ) )
Super User
Super User

Re: Delete or not count values from a table when the value is in another table

Highlighted
ChandeepChhabra Established Member
Established Member

Re: Delete or not count values from a table when the value is in another table

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

 

Capture.PNG