Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

MdxScript (Sales) (1890,3) Calculation error in measure.

hi,

created this DAX measure in Azure Analysis Server (AAS):

IF
(
[WarrantyLimit1]=0,
SUM([Measurement1])*'Sales'[InvoicedQty],

IF(
LEFT([Meter3],4) = "TRAB",
SUM([Measurement3])*'Sales'[InvoicedQty],
0
)
)

The script is picking data from Product as well as Sales and in AAS it is not giving issues. When uploading this measure to Power BI web, i get an error message: 

Couldn' t load the data for this visual

MdxScript(Sales) (1890, 3) Calculation error in measure; the valule for Measurement3 cannot be determined, either the column doesn't exist or there is no current row for this column.

Checked everything, it seems everything is available, but still this error message remains.

Anyone an idea how to solve??

thanks!!!

  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Perhaps the same column name is used for another column in another table which makes it impossible for the engine to identify which is which without specifying the table name. The standard is to specify the table name along with the column name when referencing a column and not to include the table name when referencing a measure. Please try

    TranspoKarton =
IF (
    [WarrantyLimit1] = 0,
    SUM ( 'Dim_Product'[Measurement1] ) * 'Sales'[InvoicedQty],
    IF (
        LEFT ( [Meter3], 4 ) = "TRAB",
        SUM ( 'Dim_Product'[Measurement3] ) * 'Sales'[InvoicedQty],
        0
    )
)

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 

    what are Measurement1, Measurement1 and Meter3? Are they measure or columns? If columns then in which table? What are the relationships between the tables? How does the visual look like. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tamerj1,

     

    thanks for swift response, Measurements1-3 are fields (columns) with values related to items which are in the table Product. The relation between Sales and Product is productkeyID, which is a number generated by the system. The visual i am using is just a table in which i added the measure:

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 
      Perhaps the same column name is used for another column in another table which makes it impossible for the engine to identify which is which without specifying the table name. The standard is to specify the table name along with the column name when referencing a column and not to include the table name when referencing a measure. Please try

      TranspoKarton =
IF (
    [WarrantyLimit1] = 0,
    SUM ( 'Dim_Product'[Measurement1] ) * 'Sales'[InvoicedQty],
    IF (
        LEFT ( [Meter3], 4 ) = "TRAB",
        SUM ( 'Dim_Product'[Measurement3] ) * 'Sales'[InvoicedQty],
        0
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        hi Tamerj1,

        thanks for your swift response but this solution did not work as well. Had to solve it in a different way by adding the calculations in the DSA. By doing so, i was able to deliver the proper info.

        Again, appreciate your support!!!

        thnnx!!

        Maarten