topic Re: How to use or function with filters in DAX Commands and Tips

How to use or function with filters

Petegdl23 — Fri, 16 Feb 2024 23:08:34 GMT

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

Re: How to use or function with filters

govindarajan_d — Sat, 17 Feb 2024 03:32:57 GMT

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!

Re: How to use or function with filters

lbendlin — Sun, 18 Feb 2024 14:58:27 GMT

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

Re: How to use or function with filters

govindarajan_d — Mon, 19 Feb 2024 00:56:54 GMT

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/

Re: How to use or function with filters

lbendlin — Mon, 19 Feb 2024 13:11:31 GMT

govindarajan_d you are right, I forgot about that limitation.

Re: How to use or function with filters

Petegdl23 — Thu, 22 Feb 2024 14:17:16 GMT

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.