Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) ) )
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] ) ) )
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,
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |