Forum Discussion
Post ProdigyUse calendar's inactive relationship in matrix visual?
My main table is related to the calendar using the following relationships to enable a date toggle (as a field parameter) in a separate tab:
Bar visual is using the field parameter below:
Parameter = {
("Completed", NAMEOF([~(Completed) Total Orders]), 0, "Completed"),
("Received", NAMEOF([~(Received) Total Orders]), 1, "Received"),
("On Time", NAMEOF([~On Time %]), 2, "On Time")
}
Naturally, when I use the Calendar[Date] field on a visual that doesn't use said field parameter, it uses the Data[Date Received] relationship since it is set as the Active relationship with the Calendar table. Is there a way for me to use the Inactive relationship instead of the Active one?
Matrix visual defaults to using the active Date Received relationship
Hi olimilo
Yes, you can not change which relationship a plain Calendar[Date] field uses by default as it will always follow the active one but you can force matrix to use inactive Date Completed relationship. Measure - CALCULATE([Your Measure], USERELATIONSHIP('Data'[Date Completed], 'Calendar'[Date])). Since a matrix unlike your bar chart is not from field parameter's NAMEOF switching.
You can create measure with USERELATIONSHIP with respect to Date Completed instead of Date Received and rest in the report will be using active relationship
6 Replies
johnt75Super User
Create a calculation group with 2 items. The Date Received calculation item should just be SELECTDMEASURE() and the Date Completed item would be
Date Completed = CALCULATE ( SELECTEDMEASURE (), USERELATIONSHIP ( 'Calendar'[Date], 'Data'[Date Completed] ) )Apply the calculation group as a filter on your matrix, and select Date Completed.
olimiloWhat exactly is a calculation group? Do you mean a measure or some other thing?
- johnt75
Calculation groups are powerful tools which allow you to capture and manipulate the filter context under which a measure is executed. One common use case is to provide time intellilgence functionality for any measure in the model, e.g. to perform year on year comparison.
You can read more about what they are and how to create them at https://learn.microsoft.com/en-us/power-bi/transform-model/calculation-groups .
- krishnakanth240
Hi olimilo
Yes, you can not change which relationship a plain Calendar[Date] field uses by default as it will always follow the active one but you can force matrix to use inactive Date Completed relationship. Measure - CALCULATE([Your Measure], USERELATIONSHIP('Data'[Date Completed], 'Calendar'[Date])). Since a matrix unlike your bar chart is not from field parameter's NAMEOF switching.
You can create measure with USERELATIONSHIP with respect to Date Completed instead of Date Received and rest in the report will be using active relationship 
v-abhinavmuCommunity Support
Hi olimilo,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to johnt75 & krishnakanth240 for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
- v-abhinavmu
Hi olimilo,
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you