Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |