Hi all
I am trying to count rows of a table (Vendors) where field (Vendor ID) length is NOT 11. I have tried the below but am getting an error:
AVendIDNoComp =
VAR a =
CALCULATE (
COUNTROWS ( Vendor ),
FILTER ( Vendor, LEN ( Vendor[Vendor ID] <> 11 ) )
)
RETURN
a
The error I am getting is:
DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values
The Vendor ID is a text field, and contains alphanumeric characters. Any advice on how I can go about resolving this issue?
Example data contained in the field would be:
V01234/US
V83627/UK36
V93756/ES
So with the above example data I would return a count of 2 using this measure. Just to add, this is a live connection to a PBI dataset, and I have no access to amend the data model/edit query.
Many thanks for all help
