Want to Add a New Column Based on Values of Other Table

[Dave1mo1]

I have two tables with related information. The "Fact" table has transaction information with the following relevant fields:

Fact Table

Date

Model

Single/Double (Either tells us whether it's a single or double unit - the only two options)

Units

Sales

Dim Table

Model

Pricing Start Date

Pricing End Date

Pricing for Single Units (for date range)

Pricing for Double Units (for date range)

What I want to do:

I want to evaluate, for each row in the fact table, what the actual pricing "should" have been.

For example:

If - Model-Fact Table matches Model - Dim Table

&&

Date falls between pricing start date & end date

&&

Single/Double = Double

Then Return:

Value in Column "Double Pricing" that matches the model and date range.

I know conceptually what I'm trying to do but don't know whether to do this in PQ or dax, or how to set up the relationships so I can reference another table when creating a new column. I'm still learning, obviously, but thought I'd be able to figure this out 😞

[amitchandak · Accepted solution]

@Dave1mo1 ,

a new column in fact

new colum =

var _unit = [Units]

return

maxx(filter(dim, Dim[Model] = Fact[Model] && Fact[Date] >= Dim[Pricing Start Date] && Fact[Date] <= Dim[Pricing End Date] ), if(_unit =1, [Pricing for Single Units],[Pricing for Double Units] ) )

[Ashish_Mathur · Accepted solution]

Hi,

Write this column in the Fac table

Price = if(fact[Single/Double]="double",calculate(sum(Dim[Pricing for Double Units]),filter(Dim,dim[model]=earlier(fact[model])&&dim[pricing start date]<=earlier(fact[Date])&&dim[pricing end date]>=earlier(fact[Date]))),blank())

[amitchandak · Accepted solution]

@Dave1mo1 ,

a new column in fact

new colum =

var _unit = [Units]

return

maxx(filter(dim, Dim[Model] = Fact[Model] && Fact[Date] >= Dim[Pricing Start Date] && Fact[Date] <= Dim[Pricing End Date] ), if(_unit =1, [Pricing for Single Units],[Pricing for Double Units] ) )