cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abartozzi Member
Member

MdxScript(Model) (16, 72)

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] ) )
)
)

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: MdxScript(Model) (16, 72)

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)
)
3 REPLIES 3
Super User
Super User

Re: MdxScript(Model) (16, 72)

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

abartozzi Member
Member

Re: MdxScript(Model) (16, 72)

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)
)

Highlighted
Super User
Super User

Re: MdxScript(Model) (16, 72)

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)
)