Numeric and Alphanumeric data loaded into model as TEXT data type but are different in measure

[JeanMariePBI]

Hello, BI seems to trea data loaded as Text data type differently based on whether the data being loaded as Text is numeric vs alphanumeric:

 have a measure that has 2 filters based on a Text data type:

When the prod id is numeric, the measure works correctly, but the measure does not work if an alphanumeric prod id is loaded to model (prod id is always being loaded to model as Text data type)

This is the complete logic for the measure:

If both prod ids are being loaded as a Text data type why does the measure work for the numeric prod id and not for the alphnumeric?

[v-xiaoyan-msft]

Hi @JeanMariePBI ,

When you load data into Power BI Desktop, it will attempt to convert the data type of the source column into a data type that better supports more efficient storage, calculations, and data visualization. For example, if a column of values you import from Excel has no fractional values, Power BI Desktop will convert the entire column of data to a Whole Number data type, which is better suited for storing integers.

This concept is important because some DAX functions have special data type requirements. While in many cases DAX will implicitly convert a data type for you, there are some cases where it will not. For instance, if a DAX function requires a Date data type and the data type for your column is Text, the DAX function will not work correctly. So, it’s both important and useful to get the correct data type for a column. 

You may refer to this article to get further help.

Data types in Power BI Desktop 

Best Regards,
Community Support Team _ Caitlyn