Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Waseem Dar]

?Appreciate some help here. Developing a BI report from Employees table. It has Hire, rehire and termination dates. I have separate Clustered Column Chart for on-board and off-board headcounts on the same page – so 2 visuals on the same page one based on hire date and the other based on termination date. 2 slicers are set up – one for company selection and other for year selection. I then added a calendar table as ‘CalendarAuto’ and linked the ‘date’ column with hire, rehire and termination dates through Manage Relationship. When I adjust the Year slicer bar – say to year 2019 – 2020, the visuals do not give correct data. The visuals should follow the year from slicer bar selection. I have attached screenshots here. What I am doing wrong?

——————————
Waseem Dar
Business Analyst
——————————

[Mark Beedle]

Hi Waseem,

There can only be 1 active relationship between the Calendar table and your Employees table.  If you check in Manage Relationship, I suspect that the relationship between Calendar and termination date is set to inactive.  Power BI (and SSAS Tabular) do not allow more than one active relationship between two tables.  If you want 1 slicer to filter both tables, based on 2 different dates/relationships, you’ll probably need to write a measure that activates the inactive relationship based on termination date.  Assuming your measure is some kind of sum on headcount, the new measure would be something like:

Terminated Headcount = CALCULATE(SUM(‘YourTableName'[Headcount]), USERELATIONSHIP(‘YourTableName'[TerminationDate],’Calendar'[Date]))

Let me know if that works for you.

——————————
Mark Beedle
Business Intelligence Developer and Administrator
TempurSealy Inc.
Lexington KY
——————————
——————————————-

[Mark Beedle]

Hi Waseem,

Just to clarify, in order to USERELATIONSHIP to work in the measure, make sure that there is a relationship (even if inactive) established between your ‘Employees’ table and ‘Calendar’ table.  I think your original post said that you established that relationship based on TerminationDate.

——————————
Mark Beedle
Business Intelligence Developer and Administrator
TempurSealy Inc.
Lexington KY
——————————
——————————————-

[Waseem Dar]

Thanks Mark, yes, I took the relationship off during my struggle to fix the issue.

——————————
Waseem Dar
Business Analyst
——————————
——————————————-

[Raul Vinuesa Arjona]

Agreed with @Mark , this practice Will solve your problem @Waseem

——————————
Raúl Vinuesa Arjona
Controller | Lean Yellow Belt | Business Intelligence
——————————
——————————————-

[Waseem Dar]

Thanks Raul for looking it to this.

——————————
Waseem Dar
Business Analyst
——————————
——————————————-