Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
3 months ago
Solved

Use 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 be...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    2 months ago

    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 months ago

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.

  • olimilo's avatar
    olimilo
    Icon for Post Prodigy rankPost Prodigy
    2 months ago

    What exactly is a calculation group? Do you mean a measure or some other thing?

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      2 months ago

      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 .