Instance-level variable that uses table-level information with Set Expression

[farmerfarmer]

I have a pivot table master visualization of transactions, and a lot of transactions have a "counter-transaction": same details (same values in various columns), but the cost is exactly the negative of the original value. To simplify the visualization, I wish to exclude such row-pairs.
I do not want to change the data load script, I would like to exclude these rows on the chart level.
For simplicity, I only exclude such pairs when there is exactly one negative and one positive value with this absolute value cost (and all other relevant column values are equal).

As an example:

Name, Text, Period, Cost
John, T2 transaction, 12, 76.2
John, T2 transaction, 12, -76.2
...

In the above case, the two rows wouldn't be shown in the visualization.

So I thought to create two master item measures, calculated for each instance:
1) one for checking how many rows there are that have the same Name, TransactionType, Period, and Abs(Cost)
2) and one for checking the number of distinct signs of Cost for such rows (if all Cost values are positive, e.g. three instances with 76.2 as cost, then this would equal one, otherwise if there are both positive and negative values, this would equal two).
I would only filter (a pair of) rows if both variables equal 2, for convenience.

I am not very familiar with set expressions (a comprehensive guide which gives a complete overview of set expressions would be of my interest), so I attempted to define the variables like this:

Count(
    {<
        [Responsible Account] = {'$(=[Responsible Account])'},
        Cost_long = {'$(=Cost_long)'},
        Period = {'$(=Period)'},
        [CT.TEXT] = {'$(=[CT.TEXT])'},

        Cost_Amount = {'$(=Abs(Cost_Amount))', '$(=-Abs(Cost_Amount))'}
    >}
    Cost_Amount //Instances matching the criteria
)

and

Count(DISTINCT
    {<
        [Responsible Account] = {'$(=[Responsible Account])'},
        Cost_long = {'$(=Cost_long)'},
        Period = {'$(=Period)'},
        [CT.TEXT] = {'$(=[CT.TEXT])'},

        Cost_Amount = {'$(=Abs(Cost_Amount))', '$(=-Abs(Cost_Amount))'}
    >}
    Sign(Cost_Amount)
)

However, neither of the variables actually measure what I want; no matter the instance details, in every case I got 0 as value (which should not even be a possible value).

How can I implement these variables?

[rwunderlich]

You didn't indicate what your Pivot table dimensions are or if there are other measures in the table. 

In a simple table with Dimensions of Name, Text, Period you could use a measure of Sum(Cost) and suppress rows with zero values. I'm guessing this is not a exact solution for your case, but perhaps you could describe more about the actual dimensions and result expressions in your problem.

-Rob

[farmerfarmer]

Yes; there are other measures. The pivot table has these dimensions and measures (slightly renamed names):

Account: master item with formula =[Responsible Account]; string
Costcenter: =Cost_long; string
Period : =Period; "integer"
Date: =date(CT.DATE); date
Billnr.: = CT.BILL; string
Text: =[CT.TEXT]; string
Tax: =[TX.TYPE]; string
Cost_Amount: =Cost_Amount; "float"

(The pivot table visualizes them hierarchically, expanding one-by-one. I wish I had authority to post an image, although I might construct an artificial example.)

The date, bill number, tax can differ, they should not be taken into account. I would only filter rows that have the same Account, Costcenter, Period, Text, and opposite Cost_Amount.

If I knew how to add the instances' field values in measure definitions, then that should lead to a solution - that is what I tried to do in my original solution, but it failed unfortunately.

[marcus_sommer]

It's not directly related to a set expression which is only an adjustment to the selection state (worked always on a column-level) else an expression-result should be compared against a condition. To get this a row-level evaluation is needed respectively any kind of (nested) if-loop. The use of master-expressions and/or variables makes such task not mandatory simpler (rather the opposite) and has only benefits if they could be re-used n times.

The complexity of such an approach depends therefore mainly on the data-set and the requirements and may become quite expensive. Personally I could imagine to apply something like:

aggr(if(count(Name&Text&Period&abs(Cost)) = 2, null(), exp), Dim1, Dim2)

used as expression or maybe slightly changed as a calculated dimension.

If there are also two positive or negative values and/or even three or more abs() values possible you would need to include more unique information to differentiate clearly between them to be able to decide which one to keep or to skip.