Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

 

 

 

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Stachu
        Community Champion
        8 years ago

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