Forum Discussion

akapelle's avatar
akapelle
Frequent Visitor
5 years ago
Solved

Multiple filter conditions on a sumx statement

I have difficulty understanding the difference in behaviour when combining a sumx statement with multiple filter conditions. The goal was to calculate the sumx of the Qty*Price per record, only for records with a specific ARTLCode and OrderTYPE. Both filter dimensions come from a one to many relation with a single crossfilter direction. 

Originally I wrapped the sumx within a calculate formula, adding one filter for each of the conditions above. On a total level the measure added up okay, let's say 5000, but when visualizing as a table with one row per ARTLCode then each ARTLCode displays that same total of 5.000.

When I add FILTER() formulas on each filter line in the calculate row, the issue is solved and the actual subtotals per article are only shown and the total remains correct. Alternatively, if I put the conditions within the sumX statement via en if() statement it's also solved but that seems not the right way.

Can someone please help me understand what could cause the difference in behaviour and basically what is a good way to determine when it's needed to use a FILTER() statement within in a CALCULATE() statement as opposed to just entering the filters without that statement as in my original code below:

My initial measure code:

 

calculate(
    sumx(
        'REP SalesOrderTransaction'
        , 'REP SalesOrderTransaction'[QtyTransaction]
            * 'REP SalesOrderTransaction'[InvoicePrice]
        )
    ,'REP ArticleDimensions'[ARTLCode]= "CST-102"
    ,'REP OrderType'[CustomerOrder]=0
)

 

 When I changed the measure and the issue was fixed:

 

TransportFee seperate = 
calculate(
    sumx(
        'REP SalesOrderTransaction'
        , 'REP SalesOrderTransaction'[QtyTransaction]
            * 'REP SalesOrderTransaction'[InvoicePrice]
        )
    ,filter('REP ArticleDimensions','REP ArticleDimensions'[ARTLCode]= "CST-102")
    ,filter('REP OrderType','REP OrderType'[CustomerOrder]=0)
)

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, akapelle ;

    There are 3 triggers for usage of the FILTER() function

    1. When resolving a conflict - If the same field is placed in a slicer and in the filter argument of the CALCULATE() function, then if a FILTER() function is not used, precedence is given to the filter argument of the CALCULATE() function (completely ignoring the selection made by the end user in the slicer).  If the FILTER() function is used in the filter argument of the CALCULATE() function, then precedence is given to the selection made by the end user in the slicer.
    2. When giving rich filter conditions - Simple filter conditions such as column value > fixed value do not warrant usage of FILTER() function.  Rich filter conditions such as column value > column value or column value > measure or measure > measure require usage of the FILTER() function.
    3. When giving OR conditions - Even though one can specify unlimited filter conditions in the CALCULATE() function, they are all AND conditions by default.  To specify OR conditions, one must use the FILTER() function.  That being said, there is a way to bypass the FILTER() function by using the || symbol to specify OR conditions
    4. FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. 

    https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter

    https://docs.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, akapelle ;

    There are 3 triggers for usage of the FILTER() function

    1. When resolving a conflict - If the same field is placed in a slicer and in the filter argument of the CALCULATE() function, then if a FILTER() function is not used, precedence is given to the filter argument of the CALCULATE() function (completely ignoring the selection made by the end user in the slicer).  If the FILTER() function is used in the filter argument of the CALCULATE() function, then precedence is given to the selection made by the end user in the slicer.
    2. When giving rich filter conditions - Simple filter conditions such as column value > fixed value do not warrant usage of FILTER() function.  Rich filter conditions such as column value > column value or column value > measure or measure > measure require usage of the FILTER() function.
    3. When giving OR conditions - Even though one can specify unlimited filter conditions in the CALCULATE() function, they are all AND conditions by default.  To specify OR conditions, one must use the FILTER() function.  That being said, there is a way to bypass the FILTER() function by using the || symbol to specify OR conditions
    4. FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. 

    https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter

    https://docs.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.