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.
I have a measure that will display two departments within a business unit that account for the most terminated/leaver employees and shows the % those two departments make-up of the terminations.
What can I add to this DAX measure so that it will display the one department name if there is only one department within the BU with turnover and display “No terminations for this BU” when there are no departments with a terminated employee within the selected business unit? (Please see DAX and Example screens below):
-------------------------------------------------------------------------------------------------
Top 2 Departments of Terminations Total =
var t2 = topn(2,dDepartment, [Termination Count],DESC)
var t2_names = CONCATENATEX(t2, dDepartment[Department Name], ", ", [Termination Count], DESC)
return
"Top 2 departments ("& t2_names &") account for " & format(divide(SUMX(t2, [Termination Count]), CALCULATE([Termination Count], all(dDepartment)),0),"0%") & " of terminations"
-------------------------------------------------------------------------------------------------
It works great as long as the business unit has 2 or greater departments with turnover.
As soon as I select a business unit that has 1 or zero departments with turnover, it displays every single department within that business unit.
Solved! Go to Solution.
Top 2 Departments of Terminations Total =
IF([Termination Count]>1,var t2 = topn(2,dDepartment,[Termination Count],DESC)
var t2_names = CONCATENATEX(t2, dDepartment[Department Name], ", ", [Termination Count], DESC)
return
"Top 2 departments ("& t2_names &") account for " & format(divide(SUMX(t2, [Termination Count]), CALCULATE([Termination Count], all(dDepartment)),0),"0%") & " of terminations"),"There are less than two terminations for the selection.")
Top 2 Departments of Terminations Total =
IF([Termination Count]>1,var t2 = topn(2,dDepartment,[Termination Count],DESC)
var t2_names = CONCATENATEX(t2, dDepartment[Department Name], ", ", [Termination Count], DESC)
return
"Top 2 departments ("& t2_names &") account for " & format(divide(SUMX(t2, [Termination Count]), CALCULATE([Termination Count], all(dDepartment)),0),"0%") & " of terminations"),"There are less than two terminations for the selection.")
@Ibendlin, I don't know how to add the filter to the code. I have tried to google adding filters to DAX but what I've tired produces errors. Is there a site you know of where I can learn enough DAX to understand how to add the filter to the var t2?
I also tried to change the visualizatioin filter "Show items when the value:"
"is greater than" but it says "contains" and doesn't let me select anything.
Add a filter to this line
var t2 = topn(2,dDepartment, [Termination Count],DESC)
to only include the departments where the termination count is greater than 0
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |