How to use or function with filters

[Petegdl23]

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. 

Channel Customer = CALCULATE(sum('Finance fact_Revenue_Lines'[Total_Amount]), or(FILTER('Finance dim_GL_Item',CONTAINSSTRING('Finance dim_GL_Item'[Product_Level_2],"Product Name")), FILTER('All dim_Customer', CONTAINSSTRING('All dim_Customer'[Customer_Number],"4066-01"))))

[govindarajan_d]

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!

[Petegdl23 · Accepted solution]

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.

[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/

[lbendlin]

@govindarajan_d you are right, I forgot about that limitation.