MdxScript(Model) (16, 72)

[Anonymous]

Hi Forum, 

i'm having this issue. Just want to highlight that this file is the copy of a previous one, with the same measure and same parametres. The only difference is that in the latest copy queries are fed from a Sharepoint location.

have a look:

MdxScript(Model) (16, 72) Calculation error in measure 'QF Pricing (ALL)'[QF Trade Ups]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

the measure is:

Current_Price_QF =
CALCULATE (
MIN ( 'QF Pricing (ALL)'[AIF]),
LASTNONBLANK (
DATESBETWEEN ( 'Date'[Date], BLANK (), LASTDATE ( 'Date'[Date] ) ),
CALCULATE ( COUNT ( 'QF Pricing (ALL)'[AIF] ) )
)
)

[Stachu]

you posted syntax for [Current_Price_QF], while error message mentions 'QF Pricing (ALL)'[QF Trade Ups], can you post that syntax as well?

[Anonymous]

QF Trade Ups =
VAR myindex=
selectedvalue('FBC and RBD Map (Core)'[Tariff Level])
return
[Current_Price_QF]
-CALCULATE([Current_Price_QF],FILTER(ALL('FBC and RBD Map (Core)'),'FBC and RBD Map (Core)'[Tariff Level]=myindex-1)
)

[Stachu · Accepted solution]

you mentioned source is now SharePoint files - could the switch affect the data types for columns? or maybe some new data was added that doesn't meet the data types? e.g. some comment below a table in Excel etc.

I assume the error is about the FILTER criteria, 'FBC and RBD Map (Core)'[Tariff Level] has some values that are not numbers and calculation breaks for them. 
if that's the case the calculation below will give an error that VALUE cannot convert some values to numbers - the fix would be to remove non numeric values from that column

QF Trade Ups =
VAR myindex=
selectedvalue('FBC and RBD Map (Core)'[Tariff Level])
return
[Current_Price_QF]
-CALCULATE([Current_Price_QF],FILTER(ALL('FBC and RBD Map (Core)'),VALUE('FBC and RBD Map (Core)'[Tariff Level])=VALUE(myindex)-1)
)