Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful Individual
2 years ago
Solved

Developing a model based on changes in a Dimension

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?

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Read about how to handle SCD2 (slow changing dimensions)

    • ExcelMonke's avatar
      ExcelMonke
      Impactful Individual
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ExcelMonke
      Impactful Individual
      2 years ago

      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