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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.