Referencing a column from a virtual table within a LOOKUPVALUE function.

[KL001]

I am attempting to iterate over a virtual table that I have created using the addcolumns function. The column I add is meant to serve as a virtual table index.  As I iterate through this table I'm attempting to grab the value of a column that existed within the original table using lookupvalue.

I receive the following error: "Search column 'Virtual Index' for LOOKUPVALUE does not exist in the same table as a result column."

Is there a way to reference this column within the table I have created?

I was planning to create a measure that would give me total time spent in a workflow excluding aborted workflows.

Index	event_name	version_label	time_stamp
5226924	workflow_started	1.4	1/12/2018 19:50
5226925	workflow_acquired_task	1.4	1/16/2018 9:50
5226927	workflow_signoff	1.4	1/16/2018 9:51
5226926	workflow_forwarded_task	1.4	1/16/2018 9:51
5226928	workflow_acquired_task	1.4	1/16/2018 15:41
5226929	workflow_rejected_task	1.4	1/17/2018 8:25
5226930	workflow_acquired_task	1.5	1/17/2018 15:31
5226931	workflow_rejected_task	1.5	1/17/2018 15:32
5226932	workflow_started	1.5	1/17/2018 15:44
5226933	workflow_acquired_task	1.5	1/18/2018 12:36
5226934	workflow_signoff	1.5	1/18/2018 12:36
5226935	workflow_forwarded_task	1.5	1/18/2018 12:36
5226936	workflow_acquired_task	1.5	1/18/2018 13:12
5226937	workflow_aborted	1.5	1/24/2018 20:21
5226938	workflow_started	1.5	1/24/2018 21:39
5226939	workflow_acquired_task	1.5	1/26/2018 7:58
5226941	workflow_signoff	1.5	1/26/2018 7:59
5226940	workflow_forwarded_task	1.5	1/26/2018 7:59
5226942	workflow_acquired_task	1.5	1/26/2018 12:27
5226943	workflow_signoff	1.5	2/6/2018 18:25
5226944	workflow_forwarded_task	1.5	2/6/2018 18:25
5226945	workflow_acquired_task	1.5	2/7/2018 16:35
5226946	workflow_forwarded_task	1.5	2/7/2018 16:36

Total Approval Time 2 =

VAR VersionOfCurrentRow =

IF (

'Documents'[r_version_label] = "CURRENT",

ROUNDDOWN ( MAX ( 'Audit Trail'[version_label] ), 1 ),

'Documents'[r_version_label]

)

RETURN

CALCULATE (

SUMX (

ADDCOLUMNS (

'Audit Trail',

"Virtual Index", RANKX ( 'Audit Trail', 'Audit Trail'[time_stamp] )

),

VAR NextWorkflowStartIndex =

LOOKUPVALUE('Audit Trail'[Index], [Virtual Index], [Virtual Index] + 1 )

VAR NextWorkflowStartsPreviousRowTimeStamp =

LOOKUPVALUE (

'Audit Trail'[time_stamp],

'Audit Trail'[Index], NextWorkflowStartIndex - 1)

RETURN

DATEDIFF (

NextWorkflowStartsPreviousRowTimeStamp,

'Audit Trail'[time_stamp],

DAY

)

),

ALL ( 'Audit Trail' ),

'Audit Trail'[version_label] > VersionOfCurrentRow - 1,

'Audit Trail'[version_label] <= VersionOfCurrentRow,

'Audit Trail'[event_name] = "workflow_started"

)

[AlexisOlson]

I'm not positive this will work, but I would try something like this:

Total Approval Time 2 =
VAR VersionOfCurrentRow =
    IF (
        'Documents'[r_version_label] = "CURRENT",
        ROUNDDOWN ( MAX ( 'Audit Trail'[version_label] ), 1 ),
        'Documents'[r_version_label]
    )
RETURN
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                'Audit Trail',
                "PrevTimeStamp",
                    MAXX (
                        'Audit Trail',
                        'Audit Trail'[time_stamp] < EARLIER ( 'Audit Trail'[time_stamp] )
                    )
            ),
            IF ( [PrevTimeStamp] <> BLANK (), 'Audit Trail'[time_stamp] - [PrevTimeStamp] )
        ),
        ALL ( 'Audit Trail' ),
        'Audit Trail'[version_label] > VersionOfCurrentRow - 1,
        'Audit Trail'[version_label] <= VersionOfCurrentRow,
        'Audit Trail'[event_name] = "workflow_started"
    )

[KL001]

Thanks Alexis, unfortunately, this results in the following error: The function MAXX cannot work with values of type Boolean.

[AlexisOlson]

Whoops. I meant to have a filter inside MAXX like this:

MAXX (
    FILTER (
        'Audit Trail',
        'Audit Trail'[time_stamp] < EARLIER ( 'Audit Trail'[time_stamp] )
    ),
    'Audit Trail'[time_stamp]
)

[ValtteriN · Accepted solution]

Hi,

Instead of using virtual index I would just FILTER out the non-desired rows and get the datediff startdate and enddate using CALCULATE in var. It is hard to know which rows you want to include in the SUMX excatly, but this logic should work.

[KL001]

I was able to accomplish this with two separate calculations posted below. I am still curious though about why I would receive an error included within my original post.