Today I was working in SSAS Tabular Mode for SQL Server 2012.

What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report:

  • Microsoft .Net Framework 1.x
  • Microsoft .Net Framework 2.x
  • Microsoft .Net Framework 3.x
  • Microsoft .Net Framework 4.x

I would have one name instead:

  • Microsoft .Net Framework

I was using the DAX Left Function, and nested inside the Left Function I was getting the count of Characters to find out where the “.x” was.

Here is an example of my DAX Function”

=LEFT([ProductName],FORMAT(SEARCH(“.x”,[ProductName],1,-1),0)-3)

This was then giving me the error:

An argument of function ‘LEFT’ has the wrong data type or has an invalid value

At the time I did not realize that in my DAX Syntax for the Search Function I had said if it is not found then give it the value of -1. So SSAS Tabular was indeed correct in giving me the above error.

In order to fix this it was simply adding the IfError DAX Function in order to get it to work:

=IFERROR(LEFT([ProductName],FORMAT(SEARCH(“.x”,[ProductName],1,-1),0)-3),[ProductName])

Now in my PowerView Report it is showing just the Specific Products and not their Version Numbers, thereby making the report look a lot better and less cluttered.