Condition with multiple columns in DAX

Question

can I somehow filter in DAX with multiple columns?

I need filter that the difference between two dates are lower than 300 days like below.

EVALUATE
(
    CALCULATETABLE
    (
        SUMMARIZE
        (
            'Sales',
            'Sales'[MaxDatum],
            'Sales'[MinDatum]
        ),
        INT('Sales'[MaxDatum] - 'Sales'[MinDatum]) < 300            
    )
)

So it failed with this error:

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

I tried another construction of that query like this, but in this case I cannot reuse the calculated member.

EVALUATE
(
    CALCULATETABLE
    (
        SUMMARIZE
        (
            'Sales',
            'Sales'[MaxDatum],
            'Sales'[MinDatum],
            "DIFF", INT('Sales'[MinDatum] - 'Sales'[MaxDatum]) 
        ),
        [DIFF] < 300        
    )
)

Is possible to do this somehow in DAX query?

Thanks for your help

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-25T20:14:19

Try this:

EVALUATE
 (
    FILTER (
        ADDCOLUMNS ( Sales, "Diff", 1 * ( Sales[MaxDatum] - Sales[MinDatum] ) ),
        [Diff] < 300
    )
)

If you are using SSAS 2016, Excel 2016 or Power BI you can use the DATEDIFF function which is a more reliable way to calculate time deltas.

Let me know if this helps.

Condition with multiple columns in DAX

1 Answers