Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Transform an Audit Trail table

Hello, I have a question on how to transform an "audit" table to make it easier to utilize..   1) We have a table of "Issues" and thier current values.   2) In this example we have an audit...
Need Help
Tips and Tricks
epresson's avatar
epresson
Frequent Visitor
2 years ago

Is there any solution here? I'm facing the exact same need. I came up with this as a calculated column:

Duration in Minutes = 
VAR _lastdate = audittable[created]
VAR _issueid = audittable[issue_id]
VAR _field = audittable[field]
VAR _prevdate = 
    CALCULATE(
        MAX(audittable[created]),
        FILTER(
            ALL(audittable),
            audittable[issue_id] = _issueid &&
            audittable[field] = _field &&
            audittable[created] < _lastdate
        )
    )
VAR _duration = DATEDIFF(_prevdate, _lastdate, MINUTE)
RETURN
    IF(ISBLANK(_prevdate), BLANK(), _duration)

It could be that the same thing can be done with a measure like this:
Duration in Minutes = 
VAR _lastdate = MAX(audittable[created])
VAR _issueid = SELECTEDVALUE(audittable[issue_id])
VAR _field = SELECTEDVALUE(audittable[field])
VAR _prevdate = 
    CALCULATE(
        MAX(audittable[created]),
        audittable[issue_id] = _issueid,
        audittable[field] = _field,
        audittable[created] < _lastdate
    )
VAR _duration = DATEDIFF(_prevdate, _lastdate, MINUTE)
RETURN
    IF(ISBLANK(_prevdate), BLANK(), _duration)
But I haven't checked if the measure works...