Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Apply alternate state without aggregation in Set Expression

Hi,

I have a two sheet app.

On sheet 1 (default state) I have a filter with dates. On sheet 2 (Alt state 1) I have a chart with with a filter with 100 companies to choose from. I want to be able to choose a company from my sheet 2 filter but at the same time pick up the date selected on sheet 1 from the default state.

My expression before applying alternate state is as follows:

Sum([Amount Outstanding Funded] * FX * [Val Date Discount]) / [Total EUR Outstanding]

I am able to apply the alternate state to the first part of my expression:

Sum({[Alt State 1]<ValuationDate = \$::ValuationDate>}([Amount Outstanding Funded] * FX * [Val Date Discount])) / [Total EUR Outstanding]

However, this does not give me the correct answer since the part of the expression after the "/" sign does not pick up the alternate state rule.

How I do I achieve this?

Thanks!

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

1) Set analysis requires an aggregation function.

2) In an expression, if you use  a field without including an aggregation function, that is equivalent to using Only(FieldName)

So, if this is a reference to a field, you could use Only( {[Alt State 1]<ValuationDate = \$::ValuationDate>} [Total EUR Outstanding]) instead of just the field name and get the same result.

If this is instead a reference to a master measure, you will have to edit the formula of the master measure to include the set analysis.

3 Replies
Champion III

Sum({[Alt State 1]<ValuationDate = \$::ValuationDate>}([Amount Outstanding Funded] * FX * [Val Date Discount])) / Sum({[Alt State 1]}[Total EUR Outstanding])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MVP

1) Set analysis requires an aggregation function.

2) In an expression, if you use  a field without including an aggregation function, that is equivalent to using Only(FieldName)

So, if this is a reference to a field, you could use Only( {[Alt State 1]<ValuationDate = \$::ValuationDate>} [Total EUR Outstanding]) instead of just the field name and get the same result.

If this is instead a reference to a master measure, you will have to edit the formula of the master measure to include the set analysis.

Contributor III
Author

Thank you so much for your replies. I kept on trying to use Only() while not realizing I was applying it to a master measure.  Edited the formula in the master measure and its working fine.