Set Analysis: use all the selections in list boxes apart from one

[tinkerz1]

Hi,

I am using list boxes to reduce a data set, but I want one list box selection to be ignored.

I am trying to not reduce the data set by any selection in:

[Logged by Group from Escalate To]

How do I do this?

Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]=$::[Logged by Group from Escalate To]>}distinct ReferenceNumber)

Thanks

Neil.

[bgerchikov]

So, add Set Analysis to disregard all other possible selections...

For example:

If(Count({1}[Escalate To from Logged by Group]) <>Count({MonthlyReport1<Year=,Month=,OrderIdD=>}[Escalate To from Logged by Group])

[tinkerz1]

Thanks, this looks like it works.

I just have one more issue

Because I am expanding the set using P and selecting all, the dimension I am using in the chart is working of the actual selections from the list box, in effect its using information from the items selected in green from [Escalate To from Logged By Group]

But the P statement below is using more data items so the pivot table loses them, I need to make the dimension TPACompany the complete data set and not allow it to reduce from the list box selection in the alternate state, the totals are correct, but I am missing 2 fields from the TPAcompany dimension.

How can I make the dimension return all distinct values regardless of selections.

This is hard to explain...

Thanks,

Count({<[Escalate To from Logged By Group]=P({1<[Escalate To from Logged By Group]={'*'}>}[Escalate To from Logged By Group]),[Date Reference Info]={'Logged Date'}>}distinct ReferenceNumber)

[bgerchikov]

Neil,

Not sure what you mean. But do you really need P? Please check if the statement below returns the same:

Count({<[Escalate To from Logged By Group]=,[Date Reference Info]={'Logged Date'}>}distinct ReferenceNumber)

[tinkerz1]

Hi,

The totals result is different, using P I get 515, which is what I am after, using the above statement I get 15.

what I think is happening is to due with the selections in the list box, the below statement selects from the $ selections, where as P increase the $ set but because the data has increased the dimension has not.

I would like to find a calculated dimension the ignores the selection in the alternate state, something like

=aggr(only({1}TPACompany),TPACompany) , but this is not working either

The result is 15

Count({<[Escalate To from Logged By Group]=,[Date Reference Info]={'Logged Date'}>}distinct ReferenceNumber)

[tinkerz1]

If I select [Escalate To from Logged By Group], The total is correct and the dimension are correct  if I don't select any TPA company, but the dimension only shows 1 TPA company if I select some data from the Escalate To from Logged By Group] field, because the only possible selections from  Escalate To from Logged By Group] to TPAcompany can be one.

But in the expression I am asking to ignore that field and use all the data in that table, but the dimension for TPAcompany only returns 1 company as per the {Escalate To from Logged By Group] , so I need the dimension to reflect the set and not the $

Hope this explains it

[bgerchikov]

Neil,

Does your expression belong to the chart with alternate state? Can you upload qvw?

[tinkerz1]

I will have to replicate the data in a qvw, might take a few hours, I will post later.