Forum Discussion

Lucy64's avatar
Lucy64
Advocate II
9 years ago
Solved

Count by Category

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?

Need Help
  • Sean's avatar
    Sean
    9 years ago

    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! :smileyhappy:

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Lucy64

     

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

     

    Cheers

     

    CheenuSing

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    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! :smileyhappy:

     

    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's avatar
      Lucy64
      Advocate II
      9 years ago

      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's avatar
        Sean
        Community Champion
        9 years ago

        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/