Creating Role Playing Dimension Tables

The other day I had the need to create 6,214 (slight exaggeration) copies of our Date dimension table to act as Role Playing Dimensions (RPD) for a dataset that I was tidying up.

The pbix file I picked up had previously done some of these, but they had copied the whole query multiple times (including the base  query being a native SQL statement being executed against the DW!), so I wasn't particularly happy about this.

First step was to get rid of the native SQL query and just create a simple query (DimDate in this case) which had the data in the right shape.

Then I created a function (below) which returned a Table with all of the DimDate columns in it, but with the column names prefixed with some other text.

This means that for each RPD query I just need to call that function.

For instance, here's the (entire) M for my "Request Created Date" query