Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dave1mo1
Helper I
Helper I

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

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 😞

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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] ) )

View solution in original post

Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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] ) )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.