Forum Discussion
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( ...
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.
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.
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]
)
)