Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure with multiple conditions and nested loops (earlier)

Hi Datanuts,

 

I have an employee records database, for which I'm trying to create a measure that counts the number of employees per manager.  This measure works as intended with the following code:

#EmpsPerMgr =
     (COUNTROWS(
          FILTER(ALL('Workers Database'),
                COUNTROWS(
                       FILTER( 'Workers Database',
                            ( EARLIER( 'Workers Database'[MgrIDNum] ) = 'Workers Database'[EmpIdNum] )
                            )
                       )
                 )
           )
      )
)
 
Now, I want to add in a condition, which allows me to not count "EVERY" employee, but I want to filter by employee type (full time vs part time as an example).  Ideally, I'd have a slicer which I could use so I could toggle employee types, but I'd settle for a hard coded type at this point.  Here's the idea of what I think it needs:
 
#EmpsPerMgr =
     (COUNTROWS(
          FILTER(ALL('Workers Database'),
                COUNTROWS(
                       FILTER( 'Workers Database', 'Workers Database'[EmpType] = "FullTime" &&
                            FILTER( 'Workers Database',
                            ( EARLIER( 'Workers Database'[MgrIDNum] ) = 'Workers Database'[EmpIdNum] )
                            )
                       )
                 )
           )
      )
)
 
I think I want this in the inner loop, as that's where the earlier is matching all employees with the same managerID.  
 
Help on syntax or suggestions how to improve?
 
thanks!
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    so....in the end I figured out the solution, in case anyone else searches for this.

     

    Namely I learned that Filter can take multiple criteria, despite the syntax definition implying 1.  2 ways to solve:

     

    1. FIND(TABLE, AND( criteria 1, criteria 2))

    2. FIND( TABLE, criteria1 && criteria2 )

     

    Key notes...answer 1, the AND function takes only 2 criteria, so you can't do 3+.  Answer 2, you can cascade the && to make multiple criteria past the first 2.

     

    Credit here for where I found the solution:

    https://blog.westmonroepartners.com/favorite-quick-easy-dax-multiple-filter-criteria/

     

    In the end I needed to have EARLIER in the inner loop for each condition, to select correctly.  Measure works beautifully....

5 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    6 years ago

    That's unnecessarily convoluted. Just make a measure Employees = countrows('Workers Database'). Want to know how many employees are with each manager? Make a matrix, put this as the value, put the manager ID field as the rows. Want to filter by employee type? Put that as another level. Or as a slicer.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks...If I was simply looking for that, a matrix would suffice I agree.  I'm using this measure in other places for other reasons, so putting a visual up to see the items doesn't solve all my issues, thus the need for a measure.

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Earlier() function is used in calculated column, measure doesn't support it.

    You could use ALLEXCEPT() or MAX() function to complete your DAX.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      thanks for the feedback...the above code is working as today as a measure...

       

      I tried the "ALLEXCEPT", but couldn't get it to work, I'm betting it's a sytanx issue.  I'll look at MAX as well to see if that works.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        so....in the end I figured out the solution, in case anyone else searches for this.

         

        Namely I learned that Filter can take multiple criteria, despite the syntax definition implying 1.  2 ways to solve:

         

        1. FIND(TABLE, AND( criteria 1, criteria 2))

        2. FIND( TABLE, criteria1 && criteria2 )

         

        Key notes...answer 1, the AND function takes only 2 criteria, so you can't do 3+.  Answer 2, you can cascade the && to make multiple criteria past the first 2.

         

        Credit here for where I found the solution:

        https://blog.westmonroepartners.com/favorite-quick-easy-dax-multiple-filter-criteria/

         

        In the end I needed to have EARLIER in the inner loop for each condition, to select correctly.  Measure works beautifully....