many to many relation

[abc_777]

Hi,

I have three table

Sales has column name Quantity, sale_price

Product list has column name product_category, product_sub_category

Inventory has column name start_quantity, scan_quantity

Sales table has many to many relation with Product table

and Sales table has many to many relation with invenroty table

no relation between inventory table and product table

I want to calculate sale_price (quantity * sale_price)

and show table as,

product_category	product_sub_category	start_quantity	scan_quantity	sale_price (Quantity * sale_price)
beef	beef bone in	5	5	50
chicken	chicken leg	2	1	40

How can i use filter. please help me for measure.

thanks

[joglidden2]

First off, get rid of nulls in both tables using transform data. Then look at your duplicates in both tables and determine if you can get rid of duplicates in at least one table. This might get you to many-one. 

[abc_777]

i know i have duplicate data that actually i can not remove. this is the issue. database designer not so good to design database otherwise this many to many relation wont created. my each table got many to many relation.

[joglidden2]

Have you thought about doing some grouping on one table? Basically, you must get to one-many on at least one side, or PBI will have no idea how to apply filters. You may be at a dead end, I'm sorry to say. 

[lbendlin]

"Sales table has many to many relation with Product table"

why are product not unique?

"Sales table has many to many relation with invenroty table"

It is generally not recommended to join fact tables directly. You usually want to have them both controlled by a dimension table (like Products)

[abc_777]

this is the issue with db admin. i know. my all table are messed up and all are with many to many relation. 

so is there any solution for this?

[lbendlin]

Use Power Query to clean the data before you load it into Power BI. Note that Power Query is case sensitive while DAX/Power BI is not. You may also need to apply trim and case correction transforms.

[abc_777]

if there is no relation between two table- Table A and Table B how can i get one column from table A to table B

[lbendlin]

Please provide sanitized sample data that fully covers your issue.  Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[abc_777]

can I have you email address so i email you sample file please

[lbendlin]

Please paste the sample data as tables - like you have already done in the original post.

[abc_777]

i have created a power bi sample file with many to many relation. but i can not attached the file here. so if i have your email or some other way to send i can do that. and it would be much more easier for you to understand as many to many relation is already created in pbi file

if you see the attached screen shot as you said not togive any screen shot but for your understaing i want Link_barcode from Con table to Summ Table.