Forum Discussion

alexa_0028's avatar
alexa_0028
Resolver II
4 years ago

Calculated Column with multiple tables connected in a star schema

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:

 

Need Help

3 Replies

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    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's avatar
    alexa_0028
    Resolver II
    4 years ago

    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.