topic Calculate all invoices except "Cancelled" in DAX Commands and Tips

Calculate all invoices except "Cancelled"

Peter65f1 — Sat, 21 Jan 2023 10:53:40 GMT

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 🙏

Re: Calculate all invoices except "Cancelled"

FreemanZ — Sat, 21 Jan 2023 11:19:41 GMT

hi Peter65f1

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"}

)

measure =

CALCULATE(

SUM(f_invoices[revenue_czk]),

f_invoices[invoice_status] <> "cancelled"

)

Re: Calculate all invoices except "Cancelled"

Peter65f1 — Sat, 21 Jan 2023 11:28:23 GMT

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 😊