Developing a model based on changes in a Dimension

[ExcelMonke]

Hi Fabric Forum,

Looking for anyone that may have some good ideas on how to best create data models based on calculations and changes in a dimension.

For some detail:

I currently have a Location Dim table, where a column identifies a department as either "Active" or "Inactive". I have measures that calculate a final result based on this "Active" / "Inactive" column. (E.g. amount of sales for all active departments. Note, in this specific case, a department can be inactive and still have sales).

The Goal: 
I want to create a model that would determine the "Active" / "Inactive" status based on a specific calculation. For example, if a department is open x days a week (where x is a variable), change it to "Active" / "Inactive"

The Problem:

I am not sure if calculated columns can handle measure calculations (based on my research, they can't). I also know that this may bog down performance because this is a calculated column that will apply to 100s of different departments

Anybody with experience or knowledge willing to shed some light on this?

[Anonymous]

Hi @ExcelMonke ,

Can you show me you sample dataset?
It's easy to reach your Goal, and here's an example for you:

"Active" / "Inactive" = 
VAR _COUNT =
CALCULATE(
    DISTINCTCOUNT('Fact Table'[Date]),
    ALLEXCEPT('Fact Table', 'Fact Table'[department])
)
RETURN
IF(
    _COUNT >= 'Parameter'[Parameter Value],
    "Active",
    "Inactive"
)

Use this DAX to create a measure or calculated column, and the final output is as below:

But as for the performance aspect, this needs to be judged specifically based on the amount of data you have and the complexity of the data structure.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ExcelMonke]

Hmm unfortunately that will not meet the needs of what I am trying to accomplish. The parameter value is not something that is user-selected. It is based off of a set of measure calculations

[lbendlin · Accepted solution]

Read about how to handle SCD2 (slow changing dimensions)

[ExcelMonke]

Thank you! This was extremely helpful for me to read up on.