Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ALEX13
Helper I
Helper I

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
ChandeepChhabra
Impactful Individual
Impactful Individual

Hi @ALEX13

 

How do you wish to see the result?

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

Regards
Zubair

Please try my custom visuals

@ALEX13

 

delete.png


Regards
Zubair

Please try my custom visuals
ricardocamargos
Continued Contributor
Continued Contributor

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 Alex

What is your expected result above?

Regards
Zubair

Please try my custom visuals

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.