Forum Discussion

SnoekL's avatar
SnoekL
Helper II
2 years ago
Solved

Slow moving Dimension

I already found multiple topics around slow moving dimensions but they do not solve my challange.

A simple example:

 

Dimension table with surragateKey:

Fact table:

Date table (disconnected):

 

The requirement is that we want to see a report with the value against the status at the point in time.

I found some topics that advised to create measures so I have created the following measures:

Amount:

**bleep** Amount =
var x = selectedvalue('Dim Date'[DateKey])
return
calculate(sum('Fact Collection'[Amount]),'Fact Collection'[DateID]<=x)
 
Status:
Status =
var selectdate = selectedvalue('Dim Date'[Date])
return
maxx(filter('DIM Connections','DIM Connections'[StartDate]<=selectdate && 'DIM Connections'[EindDate]>selectdate),'DIM Connections'[Status])
 
This works when using it in a table:

 

The problem now is it doesn't work when I want to create a slicer on the status as I can't use a measure value in the slicer and using the status from the dimension won't give the same results.

And it also doesn't work when I want to create a matrix with the status in the row header as the measure can't be added there.

 

What would be best practise when working with Slow moving dimensions and wanting to use the values from the dimensions in slicers and the value should be based on the dimension value at a moment in time (as of date).?

Need Help

1 Reply