Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 😞
Solved! Go to Solution.
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] ) )
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())
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())
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] ) )
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |