10 Replies Latest reply on Sep 16, 2016 12:50 PM by Alex Xu

# How to find / count Dimension based on Measure value

Hi all,  I'm stumped on this one and hope it's an easy solution for the experts.  The data/viz I'm working with is below.  The goal is to provide a count of projects by current phase.  Current phase is identified by the earliest Plan Completion date having either Null Actual Completion or OpenFilter='Open'.

The example below would return 1 project in Measure and 1 project in Improve.  Any assistance is much appreciated - Chris • ###### 1. Re: How to find / count Dimension based on Measure value

Hi Christopher,

Does this work?

IF ISNULL([Actual Completion]) OR [OpenFilter] = 'Open' THEN 1 END

• ###### 2. Re: How to find / count Dimension based on Measure value

Its just countd(Project Name) but you can't have Project Name in rows.

• ###### 3. Re: How to find / count Dimension based on Measure value

Steven, thanks for the suggestion.  That identifies all the open phases but i need to identify the earliest open phase • ###### 4. Re: How to find / count Dimension based on Measure value

Thanks Luciano.  That will give me the total project count but I need if further broken down into project count by phase - where phase is identified by Phase Name with min(Plan Completion) and null actual completion.

• ###### 5. Re: How to find / count Dimension based on Measure value

Attaching an example file.

• ###### 6. Re: How to find / count Dimension based on Measure value

This was a tough one!

Change the formula for "ForumSuggestion" to the following, which will make it a dimension:

IF ISNULL([Actual Completion]) OR [OpenFilter] = 'Open' THEN [Project Name] END

Then your Phase Count field will be the following:

COUNTD([ForumSuggestion])-LOOKUP(COUNTD([ForumSuggestion]),-1)

These work under the assumption that if a current phase is open ALL previous phases have been completed.

• ###### 7. Re: How to find / count Dimension based on Measure value

Brilliant!  Thanks for the help

• ###### 8. Re: How to find / count Dimension based on Measure value

you can also use LEVEL OF DETAILS to find the earliest plan completion that fits the requirement

code for ForumSuggestion below:

If [Plan Completion] = { FIXED [Project Name]:

MIN(

IF (ISNULL([Actual Completion]) OR [OpenFilter] = 'Open')

THEN [Plan Completion]

END

)}

THEN 1

END

• ###### 9. Re: How to find / count Dimension based on Measure value

Thanks Alex.  I'm on 9.2 at work so I can't open the file but I'll check it out on my home PC this eve.

• ###### 10. Re: How to find / count Dimension based on Measure value

i didn't change anything but edited your ForumSuggestion with the code above, then you will see the magic happen:)

but here you go. the 9.2 version