Forum Discussion

jon_w's avatar
jon_w
Regular Visitor
2 years ago

Running total with inactive relationship

I clearly have some way to go before I understand DAX, but I'm struggling to get a running total when using an inactive date relationship. This is my DAX: zTest - Running Total 2 = CALCULATE( ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Yeah that would make sense, effectively you can use inactive relationships in a measure and that specific measure will use the specified inactive relationship. However, the table with your date field is not using it, so it will display all of the values that it has in it's date column as there is no actual filter context outside of the measure where the relationship is specified. I don't think adding a past/present column would be a problem but if you'd rather not I'd probably need to see the whole data model to try and streamline it a bit so that the relationship can be active and you can get the behavior you're after without the need for a past/present column.

jon_w's avatar
jon_w
Regular Visitor
2 years ago

Yep, but in my original query I make that relationship active:

zTest - Running Total 2 =
CALCULATE(
    '_Measures'[Contact: Active Count],
    USERELATIONSHIP('Contact Acquired'[Acquisition Date], 'Date Dimension'[Calendar Date]),
    FILTER(
        ALL('Date Dimension'),
        'Date Dimension'[Calendar Date] <= MAX('Date Dimension'[Calendar Date])
        && 'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date])
    ),
    'Contact Acquired'
)

So I'm not sure why it's not applying this filter:
'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date]

My Date dimension is linked to 5 other tables so I'm unable to make that relationship active in the model, I have considered creating a separate Date Dimension table, but I feel like it should be possible without that. Also, feels like a good exercise to understand how DAX actually works.
  • jon_w's avatar
    jon_w
    Regular Visitor
    2 years ago

    So I've got this working through the use of some variables and an IF statement, but it feels clunky - I can't believe this is the best way to achieve this:

    zTest - Running Total 3 =
    VAR _LastAcquisitionDate =
        CALCULATE(
            MAX('Contact Acquired'[Acquisition Date]),
            REMOVEFILTERS('Contact Acquired')
        )
    VAR _LastVisibleDate =
        MAX('Date Dimension'[Calendar Date])
    VAR _isVisible =
        _LastVisibleDate <= _LastAcquisitionDate
    RETURN
    IF (
        _isVisible,
        CALCULATE(
            DISTINCTCOUNT('Contact Acquired'[contactid]),
            USERELATIONSHIP('Date Dimension'[Calendar Date], 'Contact Acquired'[Acquisition Date]),
            FILTER(
                ALLSELECTED('Date Dimension'[Calendar Date]),
                ISONORAFTER('Date Dimension'[Calendar Date], MAX('Date Dimension'[Calendar Date]), DESC)
            ),
            'Contact Acquired'[Acquisition Date]
        )
    )