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
Highlighted
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.

Highlighted
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

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

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,150 guests
Please welcome our newest community members: