Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter function and Previous month

Hi all,

 

Im trying to calculate last month, to compare to current month but I cant understand why it doesnt work! It just returns "Blank". 

 

CALCULATE(DISTINCTCOUNT('Export Data '[OrderId]),FILTER('Export Data ','Export Data SE'[ProductName] = "X" || 'Export Data '[ProductName] = "Y"), PREVIOUSMONTH(DATES[Date]))
 
The dax works fine if I remove the filter function! The slicer I´m using is from the "Dates" table, and there is a working relationship between "Export data" and "Dates"
 
Regards,
Niclas
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hi Niclas,

    The reason for this behaviour is:

    • The 2nd argument of CALCULATE includes all columns of the (filtered) 'Export Data' table, which therefore includes the dates in the original filter context (the expanded 'Export Data' table includes the columns of DATES as well as all columns in 'Export Data').
    • The 3rd argument of CALCULATE includes the dates in the previous month.
    • Since the dates present in these two filter arguments don't intersect, the measure result is blank.

    As a general principle, it's best to filter on specific columns, rather than tables. In this case, I would suggest filtering the ProductName column rather than the 'Export Data' table.

    A measure like either of the below measures should return the expected result.

    KEEPFILTERS can be used if you want the ProductName filter to intersect with existing ProductName filters.

    (Note: I assumed that the only tables involved are Export Data and DATES. I'm assuming 'Export Data SE' was a typo(?) Also used IN operator.)

     

     

    Fixed Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Export Data'[OrderId] ),
    KEEPFILTERS ( 'Export Data'[ProductName] IN { "X", "Y" } ),
    PREVIOUSMONTH ( DATES[Date] )
)
    Fixed Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Export Data'[OrderId] ),
    'Export Data'[ProductName] IN { "X", "Y" },
    PREVIOUSMONTH ( DATES[Date] )
)

     

     

    Regards,

    Owen

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    Hi Niclas,

    The reason for this behaviour is:

    • The 2nd argument of CALCULATE includes all columns of the (filtered) 'Export Data' table, which therefore includes the dates in the original filter context (the expanded 'Export Data' table includes the columns of DATES as well as all columns in 'Export Data').
    • The 3rd argument of CALCULATE includes the dates in the previous month.
    • Since the dates present in these two filter arguments don't intersect, the measure result is blank.

    As a general principle, it's best to filter on specific columns, rather than tables. In this case, I would suggest filtering the ProductName column rather than the 'Export Data' table.

    A measure like either of the below measures should return the expected result.

    KEEPFILTERS can be used if you want the ProductName filter to intersect with existing ProductName filters.

    (Note: I assumed that the only tables involved are Export Data and DATES. I'm assuming 'Export Data SE' was a typo(?) Also used IN operator.)

     

     

    Fixed Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Export Data'[OrderId] ),
    KEEPFILTERS ( 'Export Data'[ProductName] IN { "X", "Y" } ),
    PREVIOUSMONTH ( DATES[Date] )
)
    Fixed Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Export Data'[OrderId] ),
    'Export Data'[ProductName] IN { "X", "Y" },
    PREVIOUSMONTH ( DATES[Date] )
)

     

     

    Regards,

    Owen

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for the help and explanation! I didnt really get what you mean with 

     

    "As a general principle, it's best to filter on specific columns, rather than tables. In this case, I would suggest filtering the ProductName column rather than the 'Export Data' table."

     

    Is it possible to use the filter function on only the column, without affecting the whole table? Or is that what "keepfilter" does?

    • OwenAuger's avatar
      OwenAuger
      Super User
      4 years ago

      You're welcome 🙂

      What I meant by that comment is that, if you want to apply a filter to specific columns within CALCULATE, you should provide a filter argument containing just those columns you want to filter, rather than all columns of the table containing those columns.

      In your example, since you want to apply a filter to 'Export Data'[ProductName], you should provide a filter argument containing just that column.

      The original expression that filtered the 'Export Data' table included all columns of that table, which had the unintended side effect of including filters corresponding to values visible in all columns of that table.

      The filter argument

       

      'Export Data'[ProductName] IN { "X", "Y" }

       

      is equivalent to

       

      FILTER (
    ALL ( 'Export Data'[ProductName] ),
    'Export Data'[ProductName] IN { "X", "Y" }
)

       

      which is a single-column table containing those ProductName values.

      The FILTER function itself is not necessarily the problem, though I would tend to use simple boolean expressions without using FILTER if I want apply specific values as a filter.

      The KEEPFILTERS function modifies the filter argument so that it is intersected with any existing filters, rather than overwriting them.

       

      This article touches on this general subject

       

      All the best!

      Owen

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks for the explanation! If you have time for a second question it would really help me:

         

        If I want to add "and" into this formula, how should i proceed? Because right now it seems like a get the same error "Blank". I want a distincount of id if productname are both X and Y. The table below should give me the value of 2.

         

         

        IDProduct
        1X
        1Y
        2X
        3X
        3X
        4X
        4Y