Filter a Date dimension for a table

[Applicable88]

Hello,

I want to limit the dimension of the date dimension for my table.

So the first condition is: Show me only the Dates with a delivery Status in the  B or M category, but the value of it cannot be 0:

=if( [Status] = 'B'or [Status]='M' and ([Deliveryvalue]<> 0, Dateofdelivery.autoCalendar.Date)

This works pretty fine. But I found out there are also Delivery positions which indeed can be of zero value. These [Deliverypositions] are always only the size of 1 and are of ZERO value. Something like a dummy delivery. I cannot combine these two so easily since there are also Deliveryposition in the size of '1' but can worth much. 

I tried something like this:

=if( [Status] = 'B'or [Status]='M' and ([Deliveryvalue]<> 0 or ( [Status] = 'B' or [Status]='M' and ([Deliveryvalue]= 0 and Deliveryposition ='1'), Dateofdelivery.autoCalendar.Date))

But I still don't get what I want. I think its either the syntax or its only possible within a aggregation á la set analysis? Please note I want a dimension not a measure. 

I hope someone has the answer.

Thank you very much in advance!

[rubenmarin]

Hi, can you try this?:

=if(([Status] = 'B'or [Status]='M') and ([Deliveryvalue]<> 0 or  Deliveryposition ='1'), Dateofdelivery.autoCalendar.Date)

[Applicable88]

Hi @rubenmarin,

thanks for the quick reply. Does it makes a difference if the "AND" between status M and Deliveryvalue is there,

or would this be different: =if(([Status] = 'B'or [Status]='M') or  Deliveryposition ='1' and ([Deliveryvalue]<> 0 ), Dateofdelivery.autoCalendar.Date)

And is there also a way to write it like set analysis, even though its a dimension of a table and not a measure?

Thanks in advance!

[rubenmarin · Accepted solution]

Hi @Applicable88, yes, it's different, in that case if ([Status] = 'B'or [Status]='M') is true it will return true for all the comparison, that will be the same as ([Status] = 'B'or [Status]='M') or  (Deliveryposition ='1' and ([Deliveryvalue]<> 0)).

Use parethesys to group the comparisons as needed, I do it even if it's not necessary to clarify understanding of the comparison.

To make it with set analysis you probably need aggr, it could be:

=Aggr(Only({<Status={'B','M'}>*(<Deliveryposition={'1'}>+<Deliveryvalue={">0<0"}>)} Dateofdelivery.autoCalendar.Date), Dateofdelivery.autoCalendar.Date)

[Applicable88]

Hello @rubenmarin ,

thank you very much for clarification. 

Best.