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 Manager] = "Yes"))

 

 But I am not sure how to 'tell' the BI to point back to the previous layer for the manager HC count. Can anyone help ? Thanks ! 

 

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.

10 Replies

  • 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.

  • afzalphatan's avatar
    afzalphatan
    Resolver I
    8 years ago

    Can you elaborate more on the base data...

     

    where is coulumn header "Is manager" and "workday id"

    • engyin2's avatar
      engyin2
      Frequent Visitor
      8 years ago

      Hi , 

       

      WD id is the employee id. I am using it to count the HC by each department and job layer. 

      And out of each department, I am identified if particular HC is a manger or not , hence column "is manager" will have "yes" or "no".

       

      I would like to create the measure to see the no. of HC by department reporting to the manager in the previous layer but encountered problem to have the measure written. 

       

      Appreciate your help and thanks.

  • afzalphatan's avatar
    afzalphatan
    Resolver 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)