Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Thanks OwenAuger for the quick reply. That is very helpful and makes sense to me.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |