Direct query message 'specified cast is not valid'

[Anonymous]

Hi,

I have two tables. Order headers and order lines. Linked by Order header ID. Table order headers are filled with customers, order numbers and currency conversion rates. Order lines contain all ordered itemnumbers, price per item and ordered qty. I made a column based calculation to calculate the total line price (LINE_PRICE = XXBI_ORDER_LINES_V[UNIT_SELLING_PRICE]*XXBI_ORDER_LINES_V[ORDERED_QUANTITY]*RELATED(XXBI_ORDER_HEADERS_V[CONVERSION_RATE]). When making a visual on ordernumber or customers trying to present the total sum line price, Power BI cannot display the visual and I get the message Can't display the visual (see attached printscreen). I tried also making a sum calculation within the order lines of the line prices (TOTAL_LINE_PRICE = SUM(XXBI_ORDER_LINES_V[LINE_PRICE]), but still the same message when trying to make a visual on customer or ordernumber

Please help!!!

[v-lili6-msft]

hi, @Anonymous

I have tested on my side, but not reproduce the issue. if the data type of three columns XXBI_ORDER_LINES_V[UNIT_SELLING_PRICE]

XXBI_ORDER_LINES_V[ORDERED_QUANTITY]

XXBI_ORDER_HEADERS_V[CONVERSION_RATE]

is numerical?

and the data type of column LINE_PRICE is numerical?

Best Regards,

Lin

[Anonymous]

Hi Lin,

Thanks for you reply! All details are defined as number. Within the query mode I checked the details and unchecked for each detailed column any negative values or blanks. Still Power BI cannot display the line price (calculated column) in any visual. I still get below message (except for the ORA number).