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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

@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
Super User

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