How to copy table AND ITS FIELD PROPERTIES to an aliased table using CALCULATETABLE

[Anonymous]

In my tabular model, I have one "true" date dimension, loaded from an SQL database. I then have around 25 copies of this date table, created using the CALCULATETABLE method.

However, I notice that when I edit any settings on the original date table in the tabular model, the change does NOT propogate to the copy of the table. Every field setting that needs to be changed needs to be done 25 times!

For example, I would like to add a new calculated column called "WeekStartDateDDMMYYYY" which has the formula =FORMAT([WeekStartDate],"dd mmm yyyy"). I think want to set the Sort By Column for this new column to be "WeekStartDate". I only want to make this change to the "true" table, and then have all other other 25 copies to pick up the Sort By Column setting.

Does anyone know a way to make a copy of a table and for all the table field properties to be copied as well?

[Greg_Deckler]

@Anonymous - Sorry, I'm not quite following this. Number one, I'm not sure I understand having 25 copies of a date table let alone how you are creating those copies. Are you right-clicking one table and then choosing Copy? Are you doing it via DAX?

[Anonymous]

Having multiple copies of a Date dimension table is a standard solution. In my fact tables, I have 25 date fields. In order to allow a report user to be able to choose to slice/filter by any or all of those 25 date dimensions, there need to be 25 date dimension tables available. Each copy is named something different (e.g. dimOrderDate, dimShippedDate, dimDeliveredDate, dimReturnedDate, etc., and each date dimension table has a relationship to different date field in the fact table(s).

Each copy of the base dimDate table is constructed using the DAX formula CALCULATETABLE, e.g.

dimShippedDate = CALCULATETABLE(dimDate)

Unless you have used Tabular Models created in Visual Studio before, you probably won't know how to answer this.

[Anonymous]

Why are you wrapping the name of the base table into CALCULATETABLE? Completely redundant. And probably even wrong. Try to do this and see what happens:

DimYourNewDateTable = dimDate

[Anonymous]

In my actualy solution, I have additional filters in the CALCULATETABLE statement. I omitted them for simplicity.

You reply promted me to try making a copy as you did, simply as dimDateCopy = dimDate.

Unfortunately, the effect was the same: none of the dimDate column properties were copied over to the new dimDateCopy column properties.

[Anonymous]

OK. You can't do this in Power BI alone. For this you'll need to use Tabular Editor and a bit of C# scripting in there to make a copy of a table with all its properties. THERE IS NO OTHER WAY. Just google for Tabular Editor and/or Daniel Otykier.