wrong calculations using a filter from many to 1 relationship (possibly filtering issues)

[Anonymous]

Hello. I am trying to solve an issue using an inactive many to one relationship I dont know what i am doing wrong though, I hope someone can help.

The scenario:

I have a "from - to" table ("produto_espelho_v2") that gives me the correspondency between two item codes in my DB. I created  calculated columns on that table to return the sales value and sales quantity from my fact table ("Venda Pricing"). In between them I have my product table with the description and item codes. All I want is for PBI to read my "from" product id (lets call it product A), check its "to" product id (product B) and return the total sales from that product (product B sales). I also want it to be done just using only the "from" product ID, so read product A filter my fact table and return my product A sales on another column.

My data structure:

My formulas for the calculated columns (for the quantity the formulas arethe same just changing the column I am getting from my fac table "venda pricing"):

Product A sales - >

mp venda  = CALCULATE(sum('Venda pricing'[venda bruta]),

USERELATIONSHIP ( 'Produto_espelho_v2'[id_interno_produto MP], Produto[id interno produto] ),

CROSSFILTER ( 'Produto_espelho_v2'[id_interno_produto MP], Produto[id interno produto], BOTH )

)

Product B sales ->

target venda = CALCULATE(sum('Venda pricing'[venda bruta]),

USERELATIONSHIP ( 'Produto_espelho_v2'[id_interno_produto target], Produto[id interno produto] ),

CROSSFILTER ( 'Produto_espelho_v2'[id_interno_produto target], Produto[id interno produto], BOTH )

)

Here is my output:

Product A calculations are fine ("MP Venda"and "mp qnt") are returned as expected.  However my product B calculations return as blank most of times and when it returns a value its the exactly same value as product A... which leads me to believe that for some reason it is filtering my fact table usint produt A id instead of product B (but for w/e reason only for a some of the rows)

Been breaking my head on this one for a couple of days, so i decided to as for help. Any clues?

[v-stephen-msft]

Hi @Anonymous ,

About USERLATIONSHIP in calculated column, you can refer to

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , are you creating calculated columns ?

I doubt USERELATIONSHIP  can work well with calculated columns

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Yes i am using creating calculated columns, I didnt know use relationship had issues with calculated columns...

How can I force a ralationship in a calculated column then ? I have another model with the same structure ans in that model, these formulae work fine... so I thought they would work fine in this context as well (te only difference is the bigger fact table )

Ill take a look on your links and try some of them out, will post later if I suceeded ! Thank you in advance