cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hchatter
Helper I
Helper I

What can I add to this DAX so it will display the correct amount of department names?

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.

ExampleOf-8.PNG

 ExampleOf-16.PNG

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.

 

 

ExampleOf-1.PNG

ExampleOf-Zero.PNG

 

 

 

1 ACCEPTED SOLUTION
hchatter
Helper I
Helper I

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

View solution in original post

3 REPLIES 3
hchatter
Helper I
Helper I

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

View solution in original post

hchatter
Helper I
Helper I

@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.FilterTop2Depts.PNG

 

lbendlin
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors