Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I have a problem in hand and would appreciate if someone can help me out...
Let me explain the scenario =>
Fact table:
Dimension table:
Now the problem with this dimension table is that it does not contain records for each month (that’s how it is modelled at back-end). It needs to be read like this –
& so on….
Hence, if I need to compute the amount (value*unit) in the fact table – It should look like this:
It cannot be done by creating a composite key combining ‘date’ & ‘product#’ and relating both the tables, as all the keys would not exist in the dimension table (amounts in red would be blank then).
Any idea how to achieve this? or the best possible options I have for this scenario?
(Of course, the data here is representative – the actual tables have huge data (in millions) & hence the solution needs to be dynamic)
Please stay safe!
Regards,
Rishi
Solved! Go to Solution.
@rishirajdeb , Assume you have date or create a date from month year
date = "01-" & [Month year] //change data type to date
New column in Fact =
var _dt = maxx(filter(dim, dim[product#] =fact[product#] && dim[Date]<=fact[Date]),Dim[Date])
return
fact[amount]* maxx(filter(dim, dim[product#] =fact[product#] && dim[Date] =_dt),Dim[value])
@rishirajdeb , Assume you have date or create a date from month year
date = "01-" & [Month year] //change data type to date
New column in Fact =
var _dt = maxx(filter(dim, dim[product#] =fact[product#] && dim[Date]<=fact[Date]),Dim[Date])
return
fact[amount]* maxx(filter(dim, dim[product#] =fact[product#] && dim[Date] =_dt),Dim[value])