Forum Discussion
How to use or function with filters
I am getting ask to modify a report. I need to report revenue for a set of customers plus any customer that has aquired some specific products.
I am trying to use the below query but I am getting the error that filter is used in a True /False expession.
I was able to solve it like this. I created two separate measures, one with filters by customer numbers and another one with products filtered. Then I created a third measure that sum boths and that's it.
5 Replies
- govindarajan_d
Super User
Hi Petegdl23 ,
FILTER function returns a table and OR function is expecting a boolean result as the input. That's the reason you are getting an error that 'filter is used in a True /False expession.'
Please try this code and see if it works:
I have used RELATED function to get the corresponding value of the Customer_Number.
Channel Customer = CALCULATE ( SUM ( 'Finance fact_Revenue_Lines'[Total_Amount] ), FILTER ( 'Finance dim_GL_Item', CONTAINSSTRING ( 'Finance dim_GL_Item'[Product_Level_2], "Product Name" ) || CONTAINSSTRING ( RELATED ( 'All dim_Customer'[Customer_Number] ), "4066-01" ) ) )Upvote and accept as a solution if it helps!
- lbendlin
You don't really need an explicit FILTER for this
Channel Customer = CALCULATE ( SUM ( 'Finance fact_Revenue_Lines'[Total_Amount] ), CONTAINSSTRING ( 'Finance dim_GL_Item'[Product_Level_2], "Product Name" ) || CONTAINSSTRING ( 'All dim_Customer'[Customer_Number] , "4066-01" ) )- govindarajan_d
I don't think it will work:
1. Given that filter conditions that use two different columns cannot be written in this way
2. OP had not used ALL in his filter function. Implicit filters apply ALL on the table before filtering
For more info:
https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/
I was able to solve it like this. I created two separate measures, one with filters by customer numbers and another one with products filtered. Then I created a third measure that sum boths and that's it.
