Data modeling with discrete dimension table

[rishirajdeb]

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 –

1. For product# 123, the value is 5 for Jan-20 as well as Feb-20. For Mar-20 onwards the new value should be 6
2. For product# 456, the value from Jan-20 onwards is 7 (until a new value is maintained in future)

& 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

@GilbertQ  @collinq  @ibarrau 

@v-luwang-msft  @v-xuding-msft 

[amitchandak · Accepted 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]

thanks @amitchandak, it's really helpful !!