Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sharing a calendar table (relationship) with 2 different dates

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)

1.PNG

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).

2.PNG

 

PLEASE HELP!!!!!!!!!!!!!! Thanks in advance. 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

 

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

image.png

 

to this (after renaming columns)

image.png

 

Then you would be able to create the same line chart w/o the need to create measures, just by using the available columns

image.png

 

Hope this is what you are looking for

 

Regards

Tom

 

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

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:

image.png

 

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

image.png

 

to this (after renaming columns)

image.png

 

Then you would be able to create the same line chart w/o the need to create measures, just by using the available columns

image.png

 

Hope this is what you are looking for

 

Regards

Tom

 

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thank you TOM!!!!!!!!! You're a life savior.... and many thanks for the file!!! 

 

Cat Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.