Dimension based on date

[fredrikg]

Hi,

I have this very simple model:

Each project has a stage the project is in (StageId) and the date when the project reached that state (StageId_When).

The data comes straight from the database, so very easy.

Now, I want this:

I have introduced a new dimension, StageExt. This is identical, except for projects with stage C (StageId = 3), yellow above:

• If the stage was entered less than 6 months ago, then StageExt = "Stage C (< 6 months)"
• If the stage was entered more than 6 months ago, then StageExt = "Stage C (>= 6 months)"

To do this, I need to:

• Create the StageExt dimension table, and manually fill with values, partially from the Stage table.
• Create a calculated column, StageExtId, with logic that assigns the value depending on the StageId and StageId_When values.

Although this is not a whole lot of work, it does get worse: I have 8 more dimensions to tackle in similar ways as Stage. Also, it is a little annoying, since this is something that is need on one single report only. All other reports will be based on the Stage dimension.

Is there a more clever way of doing this? It feels like something that perhaps can be solved in the report itself, and not in the model?

/Fredrik

[v-eachen-msft · Accepted solution]

Hi @fredrikg ,

Your "StageExt" column is based on "StageId" column and "StageId_When" column which are in the Project table. You could create a calculated column "StageExId"in the table "Peoject". It will generate data automaticly based on the following DAX.

StageExtId=
SWITCH (
    TRUE (),
    Project[StageID] = 3
        && DATEDIFF ( Project[StageID_when], TODAY (), MONTH ) <= 6, 3,
    Project[StageID] = 3
        && DATEDIFF ( Project[StageID_when], TODAY (), MONTH ) > 6, 4,
    Project[StageID]
)

Then you could create a new table based on this column.

StageExt =
DISTINCT(Project[StaheExtId])

Now you could create a new column in this new table to get Stage name with IF() or SWITCH() function.

The steps of creating tables and columns cannot be omitted, but at least you can automatically generate data without manually filling.