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.

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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Share your source dataset and show the expected result.

  • engyin2's avatar
    engyin2
    Frequent Visitor
    8 years ago

    Hi Ahish, 

     

    Here is the example of the source and the expected result. Thanks.

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      I cannot see the column headings - what is O9? N8?

      • engyin2's avatar
        engyin2
        Frequent Visitor
        8 years ago