hi

[abc_777]

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  

Opening Bal QTY = SUMX(RELATEDTABLE('bm_retail_t invtrackingsummary'), 'bm_retail_t invtrackingsummary'[ScanQty]) 

as you saw in the image I got the same result 395.33 which is actually the total.

but the result should be 11.10 for that Beef Burger and barcode is 15005

I guess the slicer filter by Store is not working.

please help me how can i achieve this

thanks

[v-rongtiep-msft]

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.

[abc_777]

will these measure works if many to many relation are built among three table?

[abc_777]

@v-rongtiep-msft ,

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

[v-rongtiep-msft · Accepted solution]

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.

[v-rongtiep-msft]

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.

[v-rongtiep-msft]

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.

[abc_777]

@v-rongtiep-msft ,

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

[abc_777]

hi @v-rongtiep-msft ,

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

[v-rongtiep-msft]

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.

[abc_777]

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

[abc_777]

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

[abc_777]

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