Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

Error when basing a measure on another measure which uses DIVIDE

Hi,

 

I am trying to get the % values of Adjusted Act Hrs by Visits_Duration using this measure ->

 

Adjusted Act Hrs % = DIVIDE(SUM(vw_PivotOTIF[Adjusted Act Hrs]),SUM(vw_PivotOTIF[Visits_Duration]))

 

This works perfectly. The problem occurs when I need to base this next measure (Visits 90-99 %) on the above (Adjusted Act Hrs %)->

 

Visits 90-99 % = CALCULATE(COUNTROWS(vw_PivotOTIF),vw_PivotOTIF[Adjusted Act Hrs %]<=99 && vw_PivotOTIF[Adjusted Act Hrs %]>=90)

 

It gives me the error -> A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

I kind of got around it by using this instead->

Act Hrs % = vw_PivotOTIF[Adjusted Act Hrs]/vw_PivotOTIF[Visits_Duration]*100

 

However this doesn't give me 100% accurate results.

 

Any idea how to I can get 100% accurate results?

 

Regards,

 

Gerry

 

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    android1

     

    Try to add the "Adjusted Act Hrs %" as variable in your "Visits 90-99 %" measure:

     

    Visits 90-99 % =
VAR AdjustedActHrs =
    DIVIDE (
        SUM ( vw_PivotOTIF[Adjusted Act Hrs] ),
        SUM ( vw_PivotOTIF[Visits_Duration] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( vw_PivotOTIF ),
        AdjustedActHrs <= 99
            && AdjustedActHrs >= 90
    )

     

    Regards,

    • android1's avatar
      android1
      Icon for Post Patron rankPost Patron
      9 years ago

      Hi Simon,

       

      Thanks for the reply. Still no luck. This is giving me the error 'The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.'

       

      Gerry