cancel
Showing results for
Did you mean:
New Member

## Calculate all invoices except "Cancelled"

Hi,

I am very new to DAX and Power Pivot and I'm trying to build a measure where I count all the invoices with status "open", "sent", "paid" and "overdue". I want my measure to ignore "cancelled" status of invoices.

I created this, but I am worried that it's inefficient because it uses Filter funcion in my measure.

=CALCULATE(SUM(f_invoices[revenue_czk]),

FILTER(f_invoices,

f_invoices[invoice_status]= "open" ||

f_invoices[invoice_status]= "paid" ||

f_invoices[invoice_status]= "sent" ||

f_invoices[invoice_status]= "overdue"

))

Is it OK to use filter in such scenario or would you suggest something to make my measure more simple and resources efficient?

Thank you 🙏

1 ACCEPTED SOLUTION
Community Champion

it is OK.

you may make it more concise like this

measure =
CALCULATE(
SUM(f_invoices[revenue_czk]),
f_invoices[invoice_status] IN  {"open", "paid", "sent" , "overdue"}
)
or
measure =
CALCULATE(
SUM(f_invoices[revenue_czk]),
f_invoices[invoice_status] <> "cancelled"
)
2 REPLIES 2
New Member

Thank you, the second one is very ellegant because it takes care of potential new invoice statuses in the future. Great solution, have a great day 😊

Community Champion

it is OK.

you may make it more concise like this

measure =
CALCULATE(
SUM(f_invoices[revenue_czk]),
f_invoices[invoice_status] IN  {"open", "paid", "sent" , "overdue"}
)
or
measure =
CALCULATE(
SUM(f_invoices[revenue_czk]),
f_invoices[invoice_status] <> "cancelled"
)