Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Greetings All,
Need help with a weird situation. I have a HR headcount file which I need to compute on monthly basis with the following details:-
1. Total New Joiners of the month --> identifier = Employment Hire Date
2. Total Leavers of the month --> identifier = Employment Termination Date (fields are blank for active employees)
Both of the above of the data points are came from a same report and I need to summarize them without tieing to any relationship. Current challenges are the numbers are not accurate due to the date relashionip, and only 1 active relationship allowed for "Manage Relationship" (calendar table created).
PLEASE HELP!!!!!!!!!!!!!! Thanks in advance.
Solved! Go to Solution.
Hey,
here you will find a pbix file.
The problem as you already mentioned, there are two date column in your table "HR". And there just can be one active releationship. To solve this I created two measures (be aware that my calendar table is related to the date column "Enter.
The measure entering
entering = DISTINCTCOUNT('People'[Enter])
The measure leaving
terminating = CALCULATE( DISTINCTCOUNT('People'[Name]) ,USERELATIONSHIP('People'[Terminate],'Calendar'[Date]) ,'People'[Terminate] <> BLANK() )
This will allow to create the following line chart:
Using USERELATIONSHIP allows to "active" another relationship in the context of the measure.
But you may also consider to unpivot your HR table using the Query Editor to achive something like this;
Transforming your HR table form this
to this (after renaming columns)
Then you would be able to create the same line chart w/o the need to create measures, just by using the available columns
Hope this is what you are looking for
Regards
Tom
Hey,
here you will find a pbix file.
The problem as you already mentioned, there are two date column in your table "HR". And there just can be one active releationship. To solve this I created two measures (be aware that my calendar table is related to the date column "Enter.
The measure entering
entering = DISTINCTCOUNT('People'[Enter])
The measure leaving
terminating = CALCULATE( DISTINCTCOUNT('People'[Name]) ,USERELATIONSHIP('People'[Terminate],'Calendar'[Date]) ,'People'[Terminate] <> BLANK() )
This will allow to create the following line chart:
Using USERELATIONSHIP allows to "active" another relationship in the context of the measure.
But you may also consider to unpivot your HR table using the Query Editor to achive something like this;
Transforming your HR table form this
to this (after renaming columns)
Then you would be able to create the same line chart w/o the need to create measures, just by using the available columns
Hope this is what you are looking for
Regards
Tom
Thank you TOM!!!!!!!!! You're a life savior.... and many thanks for the file!!!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |