Expression with modifier and if function

[Neha7]

Hello,

I'm trying to create a measure to get a count of all unique sales.

My table has columns like: OrderDate, SaleID,DeliveryDate, ProductID, Product Description, Status,Delivery Type,Country, State, City, Quantity, Price.

I have an existing master item to count unique sales with certain modifiers as below:

count(distinct{<
                         Country-={'Abcde'}

                       ,DeliveryType={$(vExcludeCODDelivery)}
                       ,Status={$(vExcludeReturnStatus)}

                    >}SaleID)

I need to modify this calculation to only consider SaleID with following criteria:

ProductID >= vMinProductID and ProductID <= vMaxProductID , where I am getting the variables filled in input box by User. Also, when no entries would be available in input box the min and max values should default to 0 and 100, respectively.

This is how I have defined the calculation to achieve new condition in a table called Details:

=aggr(

if(

       ProductID>= (if(Len(Trim('$(vMinProductID )')) = 0,0,$(vMinProductID )))

            and

            ProductID<= (if(Len(Trim('$(vMaxProductID )')) = 0,6650,$(vMaxProductID )))

            ,

ProductID)

,ProductID)

I am unsure how to create a new expression taking all conditions into consideration.

I would appreciate all advises.

Thank you.

[qv_testing]

may be this

count(distinct{<Country-={'Abcde'}, ProductID= {">=$(vMinProductID) <= $(vMaxProductID)"},
,DeliveryType={$(vExcludeCODDelivery)},Status={$(vExcludeReturnStatus)}>}SaleID)

[marcus_sommer]

I wouldn't use an if-loop for it else including it within the set analysis like suggested from @qv_testing + defining there the default-value, for example with: 

... ProductID= {">=alt($(vMinProductID), Default1)<= alt($(vMaxProductID), Default2)"} ...

Instead of alt() you may also use rangemin() and rangemax() to define the thresholds. Further by aware that a variable-call to a variable without any content respectively being EMPTY or NULL may fail by applying a $-sign expansion - in this case just skip it.

Beside of this it's often far more expedient not to implement such logic else to let the user select the wanted values in the field, just by applying a search like: >=MinValue<=MaxValue. Setting instead variable-values isn't easier or less work - it's just a detour with more complexity and less comprehension.