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.

afzalphatan's avatar
afzalphatan
Icon for Resolver I rankResolver I
8 years ago

Hi... 

First you need to change the Layer format to whole number format. 

 

Below is the dax formula and pic demonstrating the req result .....

 

Hope you enjoy it..

 

 

Span Final:=
Var Numerator = IF(HASONEVALUE(Table1[Layer]), 
                    CALCULATE(COUNTROWS(Table1), FILTER(ALL(Table1), Table1[Layer] = VALUES(Table1[Layer]) + 1)))
Var Denominator = CALCULATE(COUNTROWS(Table1), Table1[Is Manager] = "Yes")

Return 
DIVIDE(Numerator, Denominator)