Forum Discussion

melhajj's avatar
melhajj
Helper I
6 years ago
Solved

Measure showing same value when plotting on table or visual

Greetings All,

 

Please bear with me as I am new to creating measures in Power BI: I have an Employee table for which I want to calculate distinct number of employees , hired employees and terminated employees based on certain filters and based on a year slicer selection (coming from Dim Date Table) , I achieved this by creating the following measures :

 

TotalHeadCount =
VAR selecteddate =
SELECTEDVALUE('Date Dim'[Date].[Year])
RETURN
CALCULATE (
DISTINCTCOUNT ( ADDC_HR_EMPLOYEE_DATA[EMPLOYEE_NUMBER] ),
YEAR(ADDC_HR_EMPLOYEE_DATA[HIRE_DATE]) <= selecteddate
)

 

TotalHireCount =
VAR selecteddate =
SELECTEDVALUE('Date Dim'[Date].[Year])
RETURN
CALCULATE (
DISTINCTCOUNT ( ADDC_HR_EMPLOYEE_DATA[EMPLOYEE_NUMBER] ),
YEAR(ADDC_HR_EMPLOYEE_DATA[HIRE_DATE]) = selecteddate
)

 

TotalTerminationCount =
VAR selecteddate =
SELECTEDVALUE('Date Dim'[Date].[Year],BLANK())
RETURN
CALCULATE (DISTINCTCOUNT
(ADDC_HR_EMPLOYEE_DATA[EMPLOYEE_NUMBER]),FILTER(ADDC_HR_EMPLOYEE_DATA,ADDC_HR_EMPLOYEE_DATA[TERMINATION_DATE].[Year]= selecteddate)
)

 

The above measures givesme the correct figures I need for the year I have selected from the slicer. My issue is when I try to plot the same on a chart or in a table : I get the same value for each month , so if the measure value for TOTALHEADCOUNT in 2019 is 8177 employess I get the same across all months, also I am confused about which field to join from my Employee Table with the Dim Date Table because I have 2 another measures that calculates HIRECOUNT and TERMINATIONCOUNT and based on HIREDATE and TERMINATIONDATE so the join is confusing me and the measure itself is confusing me.

 

Any guidance would be appreciated.

 

 

 

 

4 Replies