Using condition in Calculatetable ()

Question

I've a problem on Table filtering while using CALCULATETABLE()

I tried to use the script with condition for CALCULATETABLE():

XeroInvoices[AmountPaid] < XeroInvoices[AmountDue]      

EVALUATE
SUMMARIZE(
    CALCULATETABLE(XeroInvoices,
        XeroInvoices[Status] = "AUTHORISED",
        XeroInvoices[DueDate] <=  TODAY(),
        XeroInvoices[AmountPaid] < XeroInvoices[AmountDue]      
            ),
        XeroInvoices[Number],
        XeroInvoices[Reference],
        XeroInvoices[Status],
        XeroInvoices[Date],
        XeroInvoices[DueDate],
        XeroInvoices[AmountPaid],
        XeroInvoices[AmountDue]
        )

but the error that i get in DAX Studio is as following:

Query (6, 30) 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 managed only to kinda achieve that I wanted only like this -- crating new column within SUMMARIZE() syntax and later filtering it in Excel:

EVALUATE
SUMMARIZE(
    CALCULATETABLE(XeroInvoices,
        XeroInvoices[Status] = "AUTHORISED",
        XeroInvoices[DueDate] <=  TODAY()       
            ),
        XeroInvoices[Number],
        XeroInvoices[Reference],
        XeroInvoices[Status],
        XeroInvoices[Date],
        XeroInvoices[DueDate],
        XeroInvoices[AmountPaid],
        XeroInvoices[AmountDue],
        "AmPaid<AmDue",XeroInvoices[AmountPaid]< XeroInvoices[AmountDue]
        )

Does anyone know what might be the reason for this Err in CALCULATETABLE() and what might be a proposed solution?

Thanks!!

score 0 · Answer 1 · answered Jul 14 '16 at 13:20

0

Check this

To filter by multiple columns you have to explicitly specify the "FILTER"

CALCULATETABLE (
    Product,
    FILTER (
        Product,
        OR ( Product[Color] = "Red", Product[Weight] > 1000 )
    )
)

answered Jul 14 '16 at 13:20

Mykhail Martsyniuk

927
7
3

Using condition in Calculatetable ()

1 Answers1