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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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 CodeSupplier Stock CodeWarehouse
310075933VA9673-0JC44

1

1265013190115013
104033190115021
104034912405381
104035412230801
1040356365099003
1040364M01-RS101
1040368223-11023
1040372213-00221
1040373RVR410B25B3
1040374SBL155663
1040375R9015007883
1040379904600097791
1040380904600097801
10403811000412351
104040112657653
104040260141611
104040460181283
1040405R9000741531
1070107PK-65X8ST52-00493
1070110R9610125131
10702023VA9673-0JC443

 

 

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

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.

1.png

 

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

@tamerj1 

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

Anonymous
Not applicable

@tamerj1 

Here's the error message:

CARUSO93_0-1661431444408.png

 

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

Here's the table:

CARUSO93_0-1661335317111.png

 

With the filter, it i get this:

CARUSO93_1-1661335643162.png

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?

Anonymous
Not applicable

@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?

CARUSO93_0-1661333388924.png

 

 

 

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

1.png

tamerj1
Super User
Super User

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.

1.png

 

Anonymous
Not applicable

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

 

 

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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors