Filter function and Previous month

[Anonymous]

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

[Anonymous]

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]

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]

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.

ID	Product
1	X
1	Y
2	X
3	X
3	X
4	X
4	Y

[OwenAuger]

No problem 🙂

If you want to apply "and" logic to multiple values in a given column, this article covers a general technique:

https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/

The article assumes that the multiple values come from a slicer, but it can be any arbitrary set of values, including a fixed set of values.

Do you want to apply this as a filter in your previous measure, rather than filtering on Product X or Y as we did before?

If so, I would split the logic into separate measures:

1. # Orders with both X & Y

# Orders with both X & Y =
VAR ProductList = { "X", "Y" }
VAR NumProducts =
    COUNTROWS ( ProductList )
VAR OrdersAndProducts =
    CALCULATETABLE (
        SUMMARIZE ( 'Export Data', 'Export Data'[OrderId], 'Export Data'[ProductName] ),
        KEEPFILTERS ( TREATAS ( ProductList, 'Export Data'[ProductName] ) )
    )
VAR OrdersWithNumProducts =
    GROUPBY (
        OrdersAndProducts,
        'Export Data'[OrderId],
        "@NumProductsPerOrder", SUMX ( CURRENTGROUP (), 1 )
    )
VAR OrdersWithAllProducts =
    FILTER ( OrdersWithNumProducts, [@NumProductsPerOrder] = NumProducts )
RETURN
    COUNTROWS ( OrdersWithAllProducts )

2. # Orders with both X & Y last month

# Orders with both X & Y last month =
CALCULATE (
    [# Orders with both X & Y],
    PREVIOUSMONTH ( DATES[Date] )
)

Is this the sort of thing you were looking for or have I gone off track? 🙂

Regards,

Owen

[OwenAuger · Accepted solution]

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