topic Re: I need your absolute expertise on how to solve this! Measures and DAX-Functions. in DAX Commands and Tips

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

Anonymous — Tue, 23 Aug 2022 09:52:18 GMT

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!!!

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

tamerj1 — Tue, 23 Aug 2022 13:52:37 GMT

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

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

Anonymous — Tue, 23 Aug 2022 14:07:13 GMT

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.

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

tamerj1 — Tue, 23 Aug 2022 14:16:13 GMT

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

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

tamerj1 — Tue, 23 Aug 2022 16:47:10 GMT

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.

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

Anonymous — Wed, 24 Aug 2022 09:34:33 GMT

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?

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

tamerj1 — Wed, 24 Aug 2022 09:58:25 GMT

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"!

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

Anonymous — Wed, 24 Aug 2022 10:07:59 GMT

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... 😕

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

tamerj1 — Wed, 24 Aug 2022 10:15:25 GMT

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

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

Anonymous — Thu, 25 Aug 2022 12:44:20 GMT

tamerj1

Here's the error message:

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

tamerj1 — Thu, 25 Aug 2022 12:48:41 GMT

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

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

Anonymous — Thu, 25 Aug 2022 12:52:58 GMT

300 thousand something...

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

tamerj1 — Thu, 25 Aug 2022 12:55:18 GMT

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.

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

Anonymous — Mon, 29 Aug 2022 08:18:04 GMT

tamerj1

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