March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
Hope you can help with this as I can't get my head around on how to solve this.
Many thanks!!!
Solved! Go to 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.
It's working!!
Many thanks for your help and expertise. Again, super impressed by your effort and knowledge into this. Much appreciated.
Hi @Anonymous
This is a performance issue. How many rows do you have?
300 thousand something...
@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.
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... 😕
@Anonymous
Can you please click on see details and share the error details?
@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?
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"!
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.
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.
@Anonymous
Ok. I'll get back to you later on tonight.
Hi @Anonymous
Would you please present the expected results based on the same sample of data? What is the relationship between the two table?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
24 | |
20 | |
16 |