cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gckcmc Member
Member

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!
 
1 ACCEPTED SOLUTION

Accepted Solutions
gckcmc Member
Member

Re: Measure with multiple conditions and nested loops (earlier)

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

View solution in original post

5 REPLIES 5
jthomson New Contributor
New Contributor

Re: Measure with multiple conditions and nested loops (earlier)

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

Re: Measure with multiple conditions and nested loops (earlier)

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.

Community Support Team
Community Support Team

Re: Measure with multiple conditions and nested loops (earlier)

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
gckcmc Member
Member

Re: Measure with multiple conditions and nested loops (earlier)

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

Re: Measure with multiple conditions and nested loops (earlier)

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors