cancel
Showing results for
Did you mean:
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 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.

1 ACCEPTED SOLUTION

Accepted Solutions
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
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

Highlighted
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

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

Hi Alex

What is your expected result above?
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?

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 Client Order 1000003077 13759224 1000004392 13722082 1000004392 13855291 1000004392 13862829 1000005625 13822518

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

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

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

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 47 members 1,043 guests
Recent signins: