Calculated column to see whether ID exists in another table?

[Anonymous]

Hi, 

I have 2 tables in SSAS tabular that are structured like the attached image. I want to add a calculated column to FACT.Sales that basically says if the companykey in FACT.Sales has any related entry in Ref.CompStatus with an ID of 4 or 2.

So based on the above my ideal output would look like this

I have got as far as used the RELATED function but not sure where to go from here?

Appreciate any advice/help!

[amitchandak · Accepted solution]

@Anonymous , a New column

var _cnt1 = countx(filter(Compstatus = compstatus[salesId] = fact[SalesId] && Compstatus[StatusID] in {2,4} ),compsatus[salesID])
return
if(isblank(_cnt1),0,1)