topic Re: Count employee with multiple assignments only one time in DAX Commands and Tips

Count employee with multiple assignments only one time

lhughes1 — Fri, 20 Nov 2020 14:46:18 GMT

My data set has mixed granularity.

For salary calculations this is fine because I need to use all 3 lines.

For health, however, I only want to count the amount one time.

In Excel, I added a column to identify only the first line for each employee. Later, I used that column in the health calculations.

The formula I used was =IF(COUNTIF($B$3:B3,B3)=1,1,0)

Now I've migrated to a data model and need to add a similar calculated column.

What is the DAX translation for the Excel formula above?

Or is there a better way to accomplish this goal?

Thank you.

Re: Count employee with multiple assignments only one time

amitchandak — Fri, 20 Nov 2020 15:28:00 GMT

lhughes1 , Try a measure like

countx(filter(summarize(Table[employee number],"_1", count(Table[Assignments])),[_1]>1),[employee number])

Re: Count employee with multiple assignments only one time

Anonymous — Fri, 20 Nov 2020 16:24:45 GMT

It's best to change the data model to something that follows Best Practices. If you do that, you'll have no problems with such calculcations. My suggestion is this. Create a dimension called Employee with columns Emp Number, Name, Health Premium. Then create a fact table Assignments with columns Emp Number, Assignment, FTE, Salary. Connect them with one-way filtering on Emp Number. All columns in fact tables should always be hidden and slicing should be only done via dimensions. Then create the required measures. [Total Health Premium] will be SUM( Employee[Health Premium] ). Easy. [Total Salary] will be SUM( Assignments[Salary] ). Easy again.

The secret to good end-user experience, fast and SIMPLE DAX lies in the model itself. If you have a bad model, you'll be always struggling with simple things and your DAX will be awful. Do you want that? No. I thought so 🙂