Calculated column based on table relationship

[Anonymous]

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.

[amitchandak · Accepted solution]

@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=czNHt7UXIe8

Or measure count(sales[value]) +0 , should also work with me item