Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver II
Resolver II

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-

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())

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:


Resolver II
Resolver II

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.

Impactful Individual
Impactful Individual

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):





Can someone please help me?

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors