Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gckcmc
Resolver I
Resolver I

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

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
v-eachen-msft
Community Support
Community Support

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.

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.

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
Solution Sage
Solution Sage

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.