Set dimension to null based on calculated value that involves variable

[jamielim]

I have a table with the following and other calculated measures:

System (dimension)	TotalAmount (Measure)	Status (Dimension)
A	100	Pending
A	0	null
B	20	null
B	0	Active

I have defined vD1 as a date variable.

TotalAmount= sum({<DueDate={"<=$(vD1)"},PostingDate={"<=$(vD1)"}>}Amount)

I want to change the System Dimension field to be a calculated dimension field such that if the TotalAmount is 0 and the Status is null, then the calculated System Dimension would be null. In the examples in the table above, the second record should show null instead of A in the System column. I want to use the System field to exclude rows where the Calculated System dimension is null from showing up.

I tried using the following expression in the System Dimension field:

if(sum({<DueDate={"<=$(vD1)"},PostingDate={"<=$(vD1)"}>}Amount))=0
and isnull(Status)=1,
null(),System)

The expression editor did not spot any error with the expression, but the table shows this field as an invalid dimension.

Any idea how to resolve? Thanks.

[Ahidhar]

try this in System Dimension 

=if(Status='null' and TotalAmount=0,null(),System)

then uncheck Include Null Values

[vincent_ardiet_]

You can try something like this, but be aware that all rows producing "null" will be aggregated together:
=coalesce(Aggr(Only({<Status-={null}>+<Amount={[=sum({<DueDate={"<=$(vD1)"},PostingDate={"<=$(vD1)"}>}Amount)>0]}>}System),System,Status),'null')

[Sivapriya_d]

you can try this if you have TotalAmount as your first measure.
=If((Column(1)=0 and len(Status)=0),Null(),System)

[jamielim · Accepted solution]

Hi,

I used part of your suggestion in my solution. 

I managed to get it to work by using the following which involves using aggr over another Dimension - Customer:

=if(len(Status)=0,
aggr(if(sum({<DueDate={"<=$(vD1)"},PostingDate={"<=$(vD1)"}>}[Amount])<>0, System ,null()),Customer)
,System )

Thanks all for your suggestion!

[jamielim]

Hi, TotalAmount is computed, so i cannot use it directly in the expression.