Count by Category

[Lucy64]

Having resolved one issue on my project, I now have come accross this one that puzzles me.

I have a line/column chart which is showing total employes for each year, what I want is for the pie charts to show the total current employes for the last date range selected, split by gender and another by age group.  What puzzles me is why I am getting negative numbers for some groups, ie by Gender the Total 25 is correct, Male 21 is correct, but why do I get a negative number for female.  Also by Age Group, all groups have a negative number but the overall total is correct.  What is happening?

[Sean]

@Lucy64 You know I really didn't want to mention this in the previous post but now I have to...

Why do you have TERMDATE table? Your relationships should be setup like in the original file you posted!

Then change your leaving Measure like this...

Leaving = 
    CALCULATE (
        COUNT ( PERSONAL[TERM_DATE] ),
        USERELATIONSHIP ( 'DATES'[Date], PERSONAL[TERM_DATE] ),
        ALL ( PERSONAL[TERM_DATE] )
    )

I think that's all I changed. Let me know if this works!

Good Luck!

EDIT: You can read more about this here

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

And I believe I also implemented something similar here...

http://community.powerbi.com/t5/Desktop/Cumulative-backlog-to-date/m-p/35845#M12981

[Lucy64]

Hi Sean, I added the second table for the Termination data as I was getting incorrect figures when I was taking the data from one table.  I will look at what you sent and see if this method works. Thanks.

[Sean]

Did you setup the second inactive relationship as it was in the sample file?

Drag TERM_DATE from Personal Table to DATE from DATES table - and you'll see the inactive relationship will be created.

Then change the Leaving Measure as above.

Let me know if you have any questions.

Also this link explains USERELATIONSHIP very well

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

[Lucy64]

Sorry, not sure what is happening but I could not get this to work linking both Hire & Termination dates from one table to the same date table.  The results are not correct, it shows ´people who left against the month they were hired, which is not what I want.

[Sean · Accepted solution]

@Lucy64

Remember with this setup your DATE table has only 1 Active Relationship with the PERSONAL table based on the HIRE_DATE!
Therefore when you filter/slice the data ONLY what matches your selection in the HIRE_DATE column will be considered!
So anytime you use the Year or Date Slicer or Timeline to limit the dates

it will filter your table ONLY considering what matches your selection in the HIRE_DATE and not the TERM_DATE!

However note that your Measures will produce the Correct Result because you use the USERELATIONSHIP function.

I created these Measures and got the same exact results as before:

Number Hired = COUNTA(PERSONAL[HIRE_DATE])

Number Fired =
CALCULATE (
    COUNTA ( PERSONAL[TERM_DATE] ),
    USERELATIONSHIP ( DATES[Date], PERSONAL[TERM_DATE] )
)

Balance = [Number Hired] - [Number Fired]

Number of Current Employess =
CALCULATE (
    [Balance],
    FILTER ( ALL ( DATES ), DATES[Date] <= MAX ( DATES[Date] ) )
)

Clear all slicers and timelines and look at your Data:

Then Select YEAR => 2016

Feb 2016 disappears in the second table (sorted by TERM_DATE) because it is filtered based on HIRE_DATE!

So when you filter remember you are filtering what matches in the HIRE_DATE not TERM_DATE!

However as you can see your Measures calculate the correct results.
So in the Matrix and the Chart the results are correct because we address this by using USERELATIONSHIP!

Hope this helps!

[Lucy64]

Hi Sean,  Thanks for the explanation, I know now what I was doing wrong, I was using the TERM_DATE field in the chart instead of the measure which was created.  Thanks for your help, I learnt alot.

[CheenuSing]

@Lucy64

Please share data model, expressions used and sample data to find a solution

Cheers

CheenuSing