I didn't realize that the DAX OR function only accepts 2 arguments compared with the similar function in Excel where a limit of arguments is set to 255. This difference only occurred to me when I received an error in my PowerPivot data model with an attempt to list more than 2 arguments for the OR functions; and only then I decided to check this DAX function syntax:
The MSDN documentation has a little remark (http://msdn.microsoft.com/en-us/library/ee634951.aspx):
The OR function in DAX accepts only two (2) arguments. If you need to perform an OR operation on multiple expressions, you can create a series of calculations or, better, use the OR operator (||) to join all of them in a simpler expression.
I liked the use of the word “better” in this section, and I made a little test with the data from the AdventureWorksDW2014 database. I pulled a small set of data from the dbo.DimCustomer table into my PowerPivot table:
DAX: OR(<logical1>,<logical2>) vs. Excel: OR(logical1,logical2,...)
The MSDN documentation has a little remark (http://msdn.microsoft.com/en-us/library/ee634951.aspx):
The OR function in DAX accepts only two (2) arguments. If you need to perform an OR operation on multiple expressions, you can create a series of calculations or, better, use the OR operator (||) to join all of them in a simpler expression.
I liked the use of the word “better” in this section, and I made a little test with the data from the AdventureWorksDW2014 database. I pulled a small set of data from the dbo.DimCustomer table into my PowerPivot table:
SELECT [CustomerKey]
,[FirstName]
,[LastName]
,[EnglishEducation]
FROM [AdventureWorksDW2014].[dbo].[DimCustomer]
Also, for this list of customers I added a column that would show me if a customer has finished a school. Actually I added two columns that showed me the same thing using two different types of OR logic (one with OR function and the second one with pipes):
Finished School A:=IF(OR([Education]="Partial College", [Education]="Partial High School"), "No", "Yes")
Finished School B:=IF(([Education]="Bachelors") || ([Education]="High School") || ([Education]="Graduate Degree"), "Yes", "No")
Then I used the [Finished School B] column to build one of the sample PowerView reports with the Customer count data:
So basically, for me this whole “number of arguments” limit for the DAX OR function was a lesson to learn, to check function syntax as well; however why this “number of arguments” limit differs with the limit of the Excel version of the function is still a mystery to me :-)
Happy data adventures!
Vest Nice blog for learning new things,thanks for such beautiful blog.
ReplyDeletebelow some new idea plz check once.
appvn