Or clause with Multiple Table Filter in Dax

DAX DAX Calculations
1

Hi ,
I am trying to simulate below Cognos Report Filter in Dax. This has OR Clause in filter from multiple tables

Where [Reason Description] LIKE ‘BSA%’ OR [ServiceTypeName] in (‘Business Serv Admin’,‘CRU’,‘Cannibalized’)

Used below Filter In Dax

FILTER( SUMMARIZECOLUMNS(‘Open jobcard reason’[Reason description],‘Service type’[Service type name]),
right(‘Open jobcard reason’[Reason description],3) =“BSA”
||
‘Service type’[Service type name] IN {“Business Serv Admin”,“CRU” ,“Cannibalized”}
)

but getting error. Any help highly appreciated.

2

@baijumohan,

What’s the error message you’re getting? Is it “The expression refers to multiple columns… cannot be converted to a scalar value“?

  • Brian
3

Hi @baijumohan, we’ve noticed that no response has been received from you since January 16, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

hi brian…yes…receiving same error

5

@baijumohan,

The reason you are getting this error is that FILTER on its own produces a table, not a scalar. If the result of your measure is not a scalar, it will throw that error message. There are two ways to handle this - you can either apply a function to the table, such as COUNTROWS, that returns a scalar value, or you can materialize that statement into a physical table using

image

With regard to the former approach, I definitely recommend checking out this video, which provides some great techniques for debugging measures involving virtual tables, such as yours:

https://forum.enterprisedna.co/t/how-to-use-virtual-tables-with-countrows-in-power-bi-dax-formula-technique/4055

I hope this is helpful.

  • Brian