Forum Discussion

engyin2's avatar
engyin2
Frequent Visitor
8 years ago
Solved

Need Help on DAX formula..

Hi  I wrote the below measure in BI to have the result of the no. of reporting as shown in excel.   Span = CALCULATE(COUNT(WD[Workday ID])) / CALCULATE(COUNT(WD[Is Manager]), FILTER(WD, WD[Is Manag...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi engyin2,

     

    First your error, it is because that the Column Layer is a Text type column. It cannot be used to minus 1 directly. To resolve this error, you just need to change the column typle to whole number.

     

    Then to achieve your requirement, you can try following measure:

     

    Span =
CALCULATE (
    COUNT ( WD[Layer] ),
    FILTER ( ALL ( WD ), WD[Layer] = SELECTEDVALUE ( WD[Layer] ) + 1 )
)
    / CALCULATE (
        COUNT ( WD[Layer] ),
        FILTER ( WD, WD[Is Manager] = "Yes" ),
        ALLEXCEPT ( WD, WD[Layer] )
    )

     

    Thanks,
    Xi Jin.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

engyin2

 

Try this MEASURE

 

Span =
CALCULATE ( COUNT ( WD[Workday ID] ) )
    / CALCULATE (
        COUNT ( WD[Is Manager] ),
        FILTER (
            ALL ( WD ),
            WD[Is Manager] = "Yes"
                && WD[Layer]
                    = SELECTEDVALUE ( WD[Layer] ) - 1
        )
    )
engyin2's avatar
engyin2
Frequent Visitor
8 years ago

Hi, 

 

Thanks for your help. But it dun really work. Any idea ? I have the below message prompted :

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi engyin2,

     

    First your error, it is because that the Column Layer is a Text type column. It cannot be used to minus 1 directly. To resolve this error, you just need to change the column typle to whole number.

     

    Then to achieve your requirement, you can try following measure:

     

    Span =
CALCULATE (
    COUNT ( WD[Layer] ),
    FILTER ( ALL ( WD ), WD[Layer] = SELECTEDVALUE ( WD[Layer] ) + 1 )
)
    / CALCULATE (
        COUNT ( WD[Layer] ),
        FILTER ( WD, WD[Is Manager] = "Yes" ),
        ALLEXCEPT ( WD, WD[Layer] )
    )

     

    Thanks,
    Xi Jin.