Forum Discussion

ALEX13's avatar
ALEX13
Icon for Helper I rankHelper I
8 years ago
Solved

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 1383...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] ) ) )
ALEX13's avatar
ALEX13
Icon for Helper I rankHelper I
8 years ago

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.

ChandeepChhabra's avatar
ChandeepChhabra
Icon for Impactful Individual rankImpactful Individual
8 years ago

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