I need your absolute expertise on how to solve this! Measures and DAX-Functions.

[Anonymous]

Hello!

Give me your best expertise on how to solve this matter.

In my PBI-project, I have thousands of rows and the following tables and columns to play around with (see sample data below):

First, column "Stock Item Code": consists of our article numbers. This column comes from a table named "SIC-Master" . 

Column "Supplier Stock Code": is as the name suggests the supplier's stock code and can be found in the "SIC-Master" table but also in the "Warehouse" table.

Column "Warehouse": 1 or 3: states which warehouse the "Stock Item Code" and the "Supplier Code" is tied to.

In total the following tables and columns applies:

                                                Table: "SIC-Master" Column/s: "Stock Item Code", "Supplier Stock Code".
                                                Table: "Warehouse", Column/s: "Warehouse", "Supplier Stock Code".

Stock Item Code	Supplier Stock Code	Warehouse
31007593	3VA9673-0JC44	1
12650131	9011501	3
1040331	9011502	1
1040349	1240538	1
1040354	1223080	1
1040356	36509900	3
1040364	M01-RS10	1
1040368	223-1102	3
1040372	213-0022	1
1040373	RVR410B25B	3
1040374	SBL15566	3
1040375	R901500788	3
1040379	90460009779	1
1040380	90460009780	1
1040381	100041235	1
1040401	1265765	3
1040402	6014161	1
1040404	6018128	3
1040405	R900074153	1
1070107	PK-65X8ST52-0049	3
1070110	R961012513	1
1070202	3VA9673-0JC44	3

In Power BI, I now want to display:

•  Which "Supplier Stock Code" and how many of them are tied to each respective "Stock Item Code". This is to determine whether there are duplicates (By duplicates I'm meaning,
  To phrase what I want to find out(display:
                                                     
                             "Do we have Stock Item Codes that has more than one Supplier Stock Code tied to it and if so                                                   which ones?  And what Warehouse are they written on?" 
  
  The tricky thing is as you can see in the sample data, "Supplier Stock Code": 3VA9673-0JC44 can be written on different "Stock Item Codes (31007593, 1070202,)" and also be tied to different "Warehouses (1, 3)".
  
  

• Lastly, is it possible to create a card showing the nr of "Stock Item Codes" that has more than 1 "Supplier Stock Code" tied to it. So, we can keep track and see whether it's just a few ones or 1000's.

Hope you can help with this as I can't get my head around on how to solve this.

Many thanks!!!

[Anonymous]

@tamerj1 

It's working!!
Many thanks for your help and expertise. Again, super impressed by your effort and knowledge into this. Much  appreciated.

[Anonymous]

@tamerj1 

Here's the error message:

[tamerj1]

Hi @Anonymous 
This is a performance issue. How many rows do you have?

[Anonymous]

300 thousand something...

[tamerj1]

@Anonymous 
Try first to keep only the # Stock_item_Code and remove the other two measures, apply the filter then add the the other two measures one by one.

[Anonymous]

Here's the table:

With the filter, it i get this:

As you can see, filter is set to "is greater than" and "1".

Hmmm... 😕

[tamerj1]

@Anonymous 
Can you please click on see details and share the error details?

[Anonymous]

@tamerj1 Wow, thanks!!!

Super impressed by your effort into this.
I really like that it's now possible to see the nr of SIC-codes.

I tried to filter  and choose the "is greater than" and 1 as we are interested in knowing which Supplier Stock Codes has more than one Stock item code/s tied to it.
But when I try to filter, the window with my table becomes blank.

Is there any way you can display only the Supplier Stock Codes that has more than one SIC-codes attached to them?

[tamerj1]

Hi @Anonymous 
It is working normal in the sample file. See screenshot below. Are sure you've selected "is greater than"? as the screenshot you've shared shows "is less than"!

[tamerj1 · Accepted solution]

Hi @Anonymous 
Apologies for the late reply. Please refer to attached file. This is one way of doing that. Other methods also possible. However, I have to mention that there is no way "based on the given information" to relate the warehouse to the Stock Item Code. Please let me know if you have any further information on this regard.

[Anonymous]

@tamerj1 

It's a 'many to many' relationship with a cross filter direction of 'both' between the tables 'SIC-Master' and 'Warehouse'.

The expected results will show the 'Supplier Stock Code', then which 'Stock Item Code/s' that are tied to each respective 'Supplier Stock Code'. Then preferably, there will also be a column displaying numerically, how many 'Stock Item Code/s'  that are tied to the 'Supplier Stock Code'. Then another column will display which warehouse that 'Supplier Stock Code' (along with the 'Stock Item Code/s' tied to it) belongs to.

Hope this makes sense, otherwise let me know and I'll try clarify even further.

[tamerj1]

@Anonymous 
Ok. I'll get back to you later on tonight.

[tamerj1]

Hi @Anonymous 
Would you please present the expected results based on the same sample of data? What is the relationship between the two table?