Forum Discussion
Calculated column based on table relationship
Hello,
I have a problem that can be summed up like this:
I have a "Item" table and a "Sales line" table, with a many to one relationship based on Item ID (each Item has zero to many Sales line . Each sales line is for one Item).
I want to have a simple True/False column (not a measure) in my Item table to know if there is at least one Sales line with this Item (ignoring all filters, I just want to know if the Item was bought at some point) and use this in a slicer and as a dimension on charts.
It seems the "Related" function in my Item tables does not work, since it is a many-to-one relationship.
The only solution I see is in powerquery and require to do a distinct load and a left join, it seems heavy for a simple need.
Anonymous , You can try a new column like
new column Flag =
var _1 = countx(relatedtable(Sales), sales[value]) +0
return
if(_1 >0,1 ,0)
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Or measure count(sales[value]) +0 , should also work with me item
1 Reply
Anonymous , You can try a new column like
new column Flag =
var _1 = countx(relatedtable(Sales), sales[value]) +0
return
if(_1 >0,1 ,0)
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Or measure count(sales[value]) +0 , should also work with me item