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

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.

Reply
mraka9
Helper III
Helper III

Search for related products within the database

Greeting,
I have a large database in which it is possible to identify related products based on: item number, group, item.
I tried to connect these items and now I want to make a table in which I will have data that are related products.

Example:
Car-12 appears in three places and its related products are visible.
I would like to have a unique table where for each item I have a list of its relatives.

The database is quite large, so I would like to ask for help on how to do it and make it optimal for use.

Thanks a lot in advance!

Image.png

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@mraka9 I wrote this calculated table:

 

Result = 	
SELECTCOLUMNS(
		GENERATE(
			ADDCOLUMNS(
				VALUES('Table'[Item]),
				"@List", CALCULATE(CONCATENATEX('Table', 'Table'[Summary data], ";"))
			),
			VAR _current_item = 'Table'[Item]
			VAR _current_list = [@List]
			RETURN
				SELECTCOLUMNS(
					FILTER(
						VALUES('Table'[Item]),
						'Table'[Item] <> _current_item
							&& CONTAINSSTRING(_current_list, 'Table'[Item])
						),
						"Related item", 'Table'[Item]
				)
			),
			"Item", 'Table'[Item],
			"Related item", [Related item]
	)

 


Maybe it will work on the full data, but on the sample I'm getting something else for Car -14.
The thing is, can you please explain why an item is a related item. For example, why car 14 has car 33 or ca4 69 as related?


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

@mraka9 can you share a sample data as a table here so I could copy paste it to PBIX

Thanks in advance

 

Item numberGroupStavkaItemSummary data
10-111Car-12Car-12;Car-14
10-111Car-14Car-12;Car-14
10-112Bike-77Bike-77;Bike-99
10-112Bike-99Bike-77;Bike-99
20-111Car-12Car-12;Car-42;Car-33
20-111Car-42Car-12;Car-42;Car-33
20-111Car-33Car-12;Car-42;Car-33
20-121Mobile-444Mobile-444;Mobile-333
20-121Mobile-333Mobile-444;Mobile-333
30-111Car-12Car-12;Car-69;Car-777
30-111Car-69Car-12;Car-69;Car-777
30-111Car-777Car-12;Car-69;Car-777
SpartaBI
Community Champion
Community Champion

@mraka9 I wrote this calculated table:

 

Result = 	
SELECTCOLUMNS(
		GENERATE(
			ADDCOLUMNS(
				VALUES('Table'[Item]),
				"@List", CALCULATE(CONCATENATEX('Table', 'Table'[Summary data], ";"))
			),
			VAR _current_item = 'Table'[Item]
			VAR _current_list = [@List]
			RETURN
				SELECTCOLUMNS(
					FILTER(
						VALUES('Table'[Item]),
						'Table'[Item] <> _current_item
							&& CONTAINSSTRING(_current_list, 'Table'[Item])
						),
						"Related item", 'Table'[Item]
				)
			),
			"Item", 'Table'[Item],
			"Related item", [Related item]
	)

 


Maybe it will work on the full data, but on the sample I'm getting something else for Car -14.
The thing is, can you please explain why an item is a related item. For example, why car 14 has car 33 or ca4 69 as related?


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thank you very much!
This is a genius solution.
You are right, the link is provided in the sample because of the smaller sample.
This is definitely the solution to my problem!
I hope it will also work on large amount of data!

Thanks again!
Greetings

SpartaBI
Community Champion
Community Champion

@mraka9 my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

Thanks again!
I see you have great ideas in the report! I will definitely take a closer look!
Kind regards

SpartaBI
Community Champion
Community Champion

@mraka9 thank you 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors