none
SQL Analysis services Tabular model Issue

    Question

  • Hi Please help me anyone , it is urgent,

    I have created measures in tabular model, but one of the measure is displaying Nan in report instated of value,

    why this is coming ,

     i am giving briefly about measures which i have created in Tabular model,

    Measure1 : IF(HASONEVALUE(UnitTable),CALCULATE(([ABC]*[XYZ])/100,'TABLE'[Unit]=values(UnitTable[Unit])),([ABC]*[XYZ])/100)

    Measure2:  IF(HASONEVALUE(UnitTable),CALCULATE(sumx(ALL('TABLE'[Category],Measure1]),'TABLE'[Unit]=values(UnitTable[Unit])),CALCULATE(sumx(ALL('TABLE'[Category]),[Measure1])))

    Measure1 is giving value but Measure2 is giving Nan instated of value

    Please help me on this

    Regards

    Sree 

    Sunday, June 08, 2014 11:41 AM

Answers

  • Hi Sree,

    Generally, when ranking or ordering a column that contains value NaN (Not a Number), you might get wrong or unexpected results. For example, when a calculation divides 0 by 0, an NaN result is returned. This is because the formula engine performs ordering and ranking by comparing the numeric values; however, NaN cannot be compared to other numbers in the column. To assure correct results, you can use conditional statements using IF function to test for NaN values and return a numeric 0 value.

    Based on the DAX your posted, it seems that the calculation in your scenario do not contain any divides 0 by 0. Please check other DAX query in your tabular model and ensure there is no any divides 0 by 0.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, June 10, 2014 6:22 AM