Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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....
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.
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....
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |