Calculated Column with multiple tables connected in a star schema

[alexa_0028]

Hi All, 

I have table related like below :
I am in data blending mode i.e. I have connected to live dataset and then added it as a local to do transformations (Direct Query Mode)

Goal : I want to categories Invoiced Sales (measure) into categories like Direct/Edirect/Others based on below logic.

Logic I want to implement:
I want to add a calculated column to Distributor table based on below rule-
New_Column=

var LIST = {"NHS","Not Applicable Distributor"}

var OTHER = if(not ('Distributor'[Distributor Name]) in OTHER_TPS_LIST,TRUE(),FALSE())

var DIRECT = if('Distributor'[Distributor Name] = "Not Applicable Distributor" && CONTAINSSTRING('Bill'[Billing Code],"EDIRECT"),TRUE(),FALSE())

return

if(OTHER=TRUE(),"OTHER",if(DIRECT=TRUE(),"EDIRECT","DIRECT")))

Problem:
I am not able to create new column like this as "Distributor" and "Bill" table are not directly related and I cannot create a measure.
As I want to see end result like below:

[alexa_0028]

Hi @YukiK 
Thanks for your reply, but in your case , you will have to add country dimension as well in a table to get the measure value as correct. I don't want to have it.


In my case, I want to have category  defined as calculated column by the logic described in my problem statement and just have a table with category and measure.
 

[YukiK]

Try creating it as a measure instead of a calculated column. That way you can reference columns in other tables. I'd use "SELECTEDVALUE()" for getting billing code. A simple example is the following screenshot (Product table and Country table are connected to Sales table):

[alexa_0028]

Can someone please help me?