Forum Discussion

Lucy64's avatar
Lucy64
Icon for Advocate II rankAdvocate 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 cha...
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:

Sean's avatar
Sean
Icon for Community Champion rankCommunity 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
    Icon for Advocate II rankAdvocate 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
      Icon for Community Champion rankCommunity 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/

       

      • Lucy64's avatar
        Lucy64
        Icon for Advocate II rankAdvocate II
        9 years ago

        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.