Measure with multiple conditions and nested loops (earlier)

[gckcmc]

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!

[v-eachen-msft]

Hi @gckcmc ,

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

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

[gckcmc]

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.

[gckcmc · Accepted solution]

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

[jthomson]

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.

[gckcmc]

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.