Measure showing same value when plotting on table or visual

[melhajj]

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.

[Mariusz]

Hi @melhajj 

You can expand your dates in power query thsi will be probably the best solution if you care about the performance.

https://www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak · Accepted solution]

@melhajj , Try it in the way given in this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[melhajj]

Thank you so much @amitchandak ! the link you provided has helped me so much , it is exactly what I needed.

Quick question: Why for CURRENTY EMPLOYEES did you use COUNTX function whereas for HIRED and TERMINATED you used COUNT ? a small explanation would be helpful for me to understand more and build on top of what you provided.

Regards,

[v-xuding-msft]

Hi @melhajj ,

The COUNT function counts the number of cells in a column that contain non-blank values. We need to use a column in it. For COUNTX function, it counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table.

You could reference the blog to learn the difference between the two functions.

https://steemit.com/countandcountxindax/@theexcelclub/daxcountandcountx-impactofusingmeasuresandcolu...