Forum Discussion
hi
HI,
I have the following diagram
where my invtrackingsummary table is connected to product_file table using barcode column and
Product Stock is also connected to product_file table using barcode column
I have SCAN_QTY column in invtrackingsummary table.
when i drag SCAN_QTY from invtrackingsummary table and put it in Product Stock report i get following result
I have created a new column in the Product Stock table using Measure
Hi abc_777 ,
Bi directional relationships are one way of solving a specific problem where you need to filter a lookup table from the contents in a data table. Not every lookup table will need this and there are other ways to solve the problem.
Try to use CROSSFILTER dax.
Create measures.
Measure = CALCULATE ( MAX ( invtrackingsummary[ScanQty] ), CROSSFILTER ( invtrackingsummary[barcode], product_file[barcode], BOTH ) )Measure3 = CALCULATE ( [Measure], CROSSFILTER ( product_file[subject], product_stock[SUBJECT], BOTH ) )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
hi,
no one got the solution? how to access from product stock table to invtracking table via product file table.
I really need this solution. as inventory data goes in invtrackingtable then opening stock is in product stock stable and relation is via product file table.
so please help me
Hi abc_777 ,
I can't understand your meaning correctly. Could you please provide a pbix file about the tables and relationships?
If privacy is involved, please provide some of the data and the desired output.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi,
here is my relationship
in this relation invtrackingsummary and product_stock both these two tables are Many to One relation with product_file. with barcode column
what i want is just to get ScanQty column form invtrackingsummary table to Product_stock table with other columns from product_stock table
please help if anyone have any idea with this many to one relationship
thanks
I have a huge amount of data and lots of measures. in the last image that you have attached showing
Cross filter direction you can make it in both directions
but I can not. when I try to make a Cross filter direction to both directions the OK button gets grayed out just beside the cancel button. so I attached the image for your understanding
thanks
Hi abc_777 ,
I have created a simple sample, please refer to it to see if it helps you.
Change the direction of the relationships to both.
Then create a measure.
Measure 2 = MAX(invtrackingsummary[ScanQty])Please learn more about relationship direction.Model relationships in Power BI Desktop
If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi Anonymous ,
you are perfectly in right place. really thank you for your effort and you understand the senario.
when i change the cross filter direction the 'OK' button is disabled
I attached screenshot. how can i make this relationship and take scanqty column to product_stock table via product_file table. as you have understnad i think we are on edge to make the solution. so please help me out
thanks
Anonymous ,
I have created 3 measures,
invtrackingsummary and product_file have relationship using barcode column
product_stock and product_file have relationship using barcode column
in you three tables i see invtrackingsummary and product_file have a relationship with barcode
and product_stock and product_file have a relationship with the subject
1. Invtracking table = MAX('bm_retail_t invtrackingsummary'[ScanQty])2. invtrackingsummary and product_file =CALCULATE (MAX ('bm_retail_t invtrackingsummary'[ScanQty] ),CROSSFILTER ('bm_retail_t invtrackingsummary'[Barcode], 'bm_retail_t product_file'[BARCODE], Both ))3. Product_file and Product_Stock =CALCULATE ([Measure],CROSSFILTER ('bm_retail_t product_file'[BARCODE], 'bm_retail_t product_stock'[BARCODE], OneWay ))my model is,when i take scanQTY from invtrackingsummary table with other column those are from product_stock then i got following visual
see scanqty got same value repeating.
If I can attach the file for you understand it would be much easier to resolve but it has full of company data.
but i guess we are closer to resolve this issue.
==========================================================
in your invtracikingsummary visual if you take the subject column from the product_stock table then it gives you error also.
in visual,
I have a few columns from invtrackingsummary and take one column from product_scan then I got an error but
vice versa, if i have few columns from product_stock and i take one column from invtrackingsummary then i got the result.
if you try the same to your the attached file you give me you also see the same
really thank you for your time and effort
thanks you and i hope you will help me out from this isssue
Hi abc_777 ,
Bi directional relationships are one way of solving a specific problem where you need to filter a lookup table from the contents in a data table. Not every lookup table will need this and there are other ways to solve the problem.
Try to use CROSSFILTER dax.
Create measures.
Measure = CALCULATE ( MAX ( invtrackingsummary[ScanQty] ), CROSSFILTER ( invtrackingsummary[barcode], product_file[barcode], BOTH ) )Measure3 = CALCULATE ( [Measure], CROSSFILTER ( product_file[subject], product_stock[SUBJECT], BOTH ) )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi abc_777 ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi abc_777 ,
You can create a both direction for the relationship by using the formula. Since there is no way to create a both direction relationship, you need to create the relationship once for each value you want to calculate.
Since you are unable to provide pbix file. Please study my formula to get the results you want for yourself.
First, you want to show product_stock.[subject] in invtrackingsummary table. You can create a measure.
Measure_subject = VAR _a = CALCULATE ( MAX ( product_file[subject] ), CROSSFILTER ( invtrackingsummary[barcode], product_file[barcode], BOTH ) ) RETURN CALCULATE ( _a, CROSSFILTER ( product_file[subject], product_stock[SUBJECT], BOTH ) )Then you want to show invtrackingsummary'[scanty] in product_stock table. So you need to create another formula to create both direstion relationship again.
Measure_scanty = VAR _b = CALCULATE ( MAX ( invtrackingsummary[ScanQty] ), CROSSFILTER ( product_stock[SUBJECT], product_file[subject], BOTH ) ) RETURN CALCULATE ( _b, CROSSFILTER ( invtrackingsummary[barcode], product_file[barcode], BOTH ) )Similarly, if you want to display another value, you have to recalculate it with the formula.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous ,
at last with 100% support from you, it's now working. this is the one I am looking for.
thank you so much, really great and appreciable for your time and effort.
once again thank you so much for this
will these measure works if many to many relation are built among three table?
