Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
10 months ago
Solved

Inactive Relationship on Date Fields

I have had to create 2 extra calendars because my visuals fall apart whenever I try to establish an Inactive Relationship between secondary date fields and my main Date calendar - this is against best practice according to all literature on the subject but it simply doesn't work for me.

 

My main (prime relationship in the data model) is Date2[Date] Calendar Table to the Cases[Created On] in the Cases Table

 

I also have a Resolution Date in the Cases Table (which is not continuous list of dates).


The first thing that goes is the month x-axis on my column chart because these months are not based on the Resolution Date, they are the main default Created Date months.

 

Fig.1.



It should look like this:

Fig.2.


I have a filter on the Fig.1. visual to show all cases resolved after 31st Mar 2025 but it still shows Jan-Feb-Mar values in the x-axis. It seems like I have no option but to add more filters on the actual month names and that is unamanageable each month.

I want to use the abbrevaited 3 letter Month names and not the full name of each month because space is a premium in my visuals.

All of these problems made me decide to build a separate calender table that directly links the Cases[Resolution Date] to Date2[Date]. 
Unfortunately I cannot share my pbix file because of data sensitivity ete.

Has anyone else had these issues and if so, how were they tackled?





 

Need Help
Show and Tell
  • mickey64's avatar
    mickey64
    10 months ago

    For your reference.

     

    Step 0: I use these data below.

     

    Step 1: I make a 'Date2' calendar table below.

        Month_Format = FORMAT([Date],"mmm")

        Month No = MONTH([Date])

     

    Step 2: I add two relationships below.

    <Active>

    <Inactive>

     

    Step 3: I make two measures and make a 'Clustered column chart' below.

        Value on Created Date = SUM('Cases'[Value])
        Value on Resolution Date = CALCULATE(SUM('Cases'[Value]),USERELATIONSHIP('Date2'[Date],'Cases'[Resolution Date]))

     

    Step 4: I make a 'Slicer' below.

     

     

     

     

8 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    10 months ago

    Hey, ArchStanton ,
    I am not sure I get your struggle, but here's how you can easily pull single calendar with two relationships:

     

    Setup:
    Date is active, ResolvedDate is inactive

     

    Measures like this:

    activeMainSum = SUM(fact5[Revenue])

InactiveSum = 
 CALCULATE(
    SUM(fact5[Revenue]),
    USERELATIONSHIP(dim_cal[Date], fact5[ResolvedDate])
 )

active and inactive together = [activeMainSum] + [InactiveSum]

    Result:

    And I always use dim_call to display dates, I created a column with the "MMM" format. Any filter you apply will act based on the date assigned to that calculation.

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      10 months ago

      Hi, I've managed to get the following to work using USERELATIONSHIP:

      YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

      I still need to manually add a FY Year filter for the visual to work if I don't this is what I see:


      This should only show Apr to Oct

      Is there anything I can do to the code that will add the 2025/26 FY filter to it automatically?



  • mickey64's avatar
    mickey64
    Super User
    10 months ago

    For your reference.

     

    Step 0: I use these data below.

     

    Step 1: I make a 'Date2' calendar table below.

        Month_Format = FORMAT([Date],"mmm")

        Month No = MONTH([Date])

     

    Step 2: I add two relationships below.

    <Active>

    <Inactive>

     

    Step 3: I make two measures and make a 'Clustered column chart' below.

        Value on Created Date = SUM('Cases'[Value])
        Value on Resolution Date = CALCULATE(SUM('Cases'[Value]),USERELATIONSHIP('Date2'[Date],'Cases'[Resolution Date]))

     

    Step 4: I make a 'Slicer' below.

     

     

     

     

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      10 months ago

      Hi, I've managed to get the following to work using USERELATIONSHIP:

      YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

      I still need to manually add a FY Year filter for the visual to work if I don't this is what I see:


      This should only show Apr to Oct

      Is there anything I can do to the code that will add the 2025/26 FY filter to it automatically?

      • mickey64's avatar
        mickey64
        Super User
        10 months ago

        For your reference.

        How about this formula below.

        YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Date2'[Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

         

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    10 months ago

    I've added a FY filter to the DAX but can anyone suggest a way of ameding the code without me having to do this?
    This will mean I will have to create a new measure in Apr 26 and it would be much better if the YTD would simply work.

    YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
        Date2[FY] = "FY2025/2026",
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    10 months ago

    Hi,

    Share some data to work with and show the result in a simple table format.  From there, we can build any visual we want.  Share data in a format that can be pasted in an MS Excel file.