Trying to write a DAX caclulate formula with filters not working.

[dacougars]

I am relatively new to power BI and I am trying to get a formula to work.  I am trying to calculate using a measure and filtering out certain codes to get the net revenue.  However when I enter the below formula I get the following error

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

=Calculate([Actual Gross Revenue],
FILTER(LEFT('Service Billed Revenue'[Account & Subaccount 2],4)="4120" ||
LEFT('Service Billed Revenue'[Account & Subaccount 2],4)="4263"))

How do I write the above formula?  If someone could help me with this would be appreciated. 

[tex628]

The filter function takes two expressions, like this:

FILTER( Table ; Filter expression )

In your example you include only the filter expression, you need to have something similar to this:

=Calculate([Actual Gross Revenue],
FILTER('Service BIlled Revenue',LEFT('Service Billed Revenue'[Account & Subaccount 2],4)="4120" ||
LEFT('Service Billed Revenue'[Account & Subaccount 2],4)="4263"))

You have to specify which table the filter is supposed to affect, in this case i assumed it to be 'Service BIlled Revenue'.

Hope this helps!

/ J

[OwenAuger · Accepted solution]

Hi @dacougars

Without changing your tables in any way, I would write this particular formula like this:

=
CALCULATE (
    [Actual Gross Revenue],
    FILTER (
        VALUES ( 'Service Billed Revenue'[Account & Subaccount 2] ),
        LEFT ( 'Service Billed Revenue'[Account & Subaccount 2], 4 ) = "4120"
            || LEFT ( 'Service Billed Revenue'[Account & Subaccount 2], 4 ) = "4263"
    )
)

The main thing to note is that FILTER takes a table as its first argument, and a boolean expression as its second argument. The table can either be a physical table or a table produced by a DAX function. In this case, it is sufficient to filter the list of values in the column 'Service Billed Revenue'[Account & Subaccount 2] which is what VALUES ( 'Service Billed Revenue'[Account & Subaccount 2] ) gives us.

You may consider adding a column to the table 'Service Billed Revenue' that contains the first 4 characters of 'Service Billed Revenue'[Account & Subaccount 2], which will allow more efficient filtering. For example you could then write a measure like:

=
CALCULATE (
    [Actual Gross Revenue],
    'Service Billed Revenue'[Account & Subaccount 2 first 4 chars]
        IN { "4120", "4263" }
)

Regards,

Owen

[dacougars]

Thanks OwenAuger for the quick reply.  That is very helpful and makes sense to me.