Max Salary for employes table which has duplicate records

[Anonymous]

Hi 

I have data like below

Emp_Num	Join Date	Hike Date	Resgn_Date	Salary
1	11-09-2018	12-12-2020	#N/A	20000
1	11-09-2018	19-09-2021	#N/A	28000
1	11-09-2018	10-10-2023	#N/A	52000
2	23-07-2021	10-10-2022	#N/A	30000
2	23-07-2021	12-12-2023	#N/A	42000
3	09-09-2020	10-10-2021	#N/A	20000
3	09-09-2020	11-06-2023	11-09-2023	25000

I want to show in a bar chart like gropus salry 20-25000 and 25-30000, 30-35000 bins and the employe where he got the last hike that employee should come in that bin

Emp_Num	Hike Date	Salary
1	10-10-2023	52000
2	23-07-2021	42000
3	11-06-2023	42000

[jeremic_nikola · Accepted solution]

The simplest solution would be to create additional column (IsMaxDateHire)

IsMaxHireDate = 
VAR _EMP = [Id]
VAR _MAXDATE = 
CALCULATE(
    MAX(Emp[HireDate]),
    REMOVEFILTERS(),
    Emp[Id] = _EMP
)
RETURN  [HireDate] = _MAXDATE

 Then in your visual simply filter when this column is true.

You can do by using measure instead of the column as well, but you will need to take care of which filters you remove and which not, if you want to implement this as a measure you need to see what are your use cases (what you will be using as dimension/filter).

Was this helpful?

[Anonymous · Accepted solution]

Thank you jeremic_nikola

It worked.

[jeremic_nikola · Accepted solution]

The simplest solution would be to create additional column (IsMaxDateHire)

IsMaxHireDate = 
VAR _EMP = [Id]
VAR _MAXDATE = 
CALCULATE(
    MAX(Emp[HireDate]),
    REMOVEFILTERS(),
    Emp[Id] = _EMP
)
RETURN  [HireDate] = _MAXDATE

 Then in your visual simply filter when this column is true.

You can do by using measure instead of the column as well, but you will need to take care of which filters you remove and which not, if you want to implement this as a measure you need to see what are your use cases (what you will be using as dimension/filter).

Was this helpful?