7 Replies Latest reply on Aug 30, 2018 12:08 PM by Jim Dehner

# How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Hey everyone,

• Facility No = Facility number (physical location)
• Parent No = is a Parent#, similar to a serial #
• Tier Level = unique to each Parent# - It's either a "Tier 1", "Tier 2", or "Tier 3" (This is a calculated field based off other measures)
• Facilities can contain multiple tiers.

1. I would like to know by Facility if this is a Single Tiered facility or Multi-Tiered facility
2. In addition, I would like to know the combination of Tiers, so if it's only Tier 1, then "Tier 1" (...or 2...or 3). If Tier 2 and 3 is at the same facility, then "Tier 2/3", if Tier 1 and 3, then "Tier 1/3", if Tier 2, then "Tier 2"...etc.
3. ***and at a higher level I need to roll up all data by "single" or "Multi-tiered" and then what combo they are by the entire population (which is over 35K records)

Hope this makes sense and THANK YOU to whoever can tell me how to do this.

Sample data below and sample workbook attached.

• ###### 1. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

To Solve 1 and 3 you can do:

# of Tiers

{Fixed [Facility #]:Countd(Parent No)}

Then another Equation to define:  If [# of Tiers] > 1 then "Multi-Tier" else "Single Tier" end

I don't have anything for your 2nd point off top of my head

• ###### 2. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

One example

Filter is only testing purpose.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Thanks Shin - as always -  worked like a charm!

• ###### 4. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Hi Kerry

You are very welcome.

Thanks,

Shin

• ###### 5. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Hi

You always have interesting questions

I think this does what you want

this will label single or multi level

this is an intermediate step needed to create the "Tier LOD"

this will give you the label denoting the tiers

the summary is this

or you can look in detail

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Hi Jim, thanks – your solution works as well!  And it also gives me the ability to take it up a level.

I appreciate it – this is great!

Kerry

• ###### 7. Re: How to denote whether a "Facility" is a single tier or multi-tiered based on dimension

Thanks Kerry