cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KL001
Frequent Visitor

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

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.

Indexevent_nameversion_labeltime_stamp
5226924workflow_started1.41/12/2018 19:50
5226925workflow_acquired_task1.41/16/2018 9:50
5226927workflow_signoff1.41/16/2018 9:51
5226926workflow_forwarded_task1.41/16/2018 9:51
5226928workflow_acquired_task1.41/16/2018 15:41
5226929workflow_rejected_task1.41/17/2018 8:25
5226930workflow_acquired_task1.51/17/2018 15:31
5226931workflow_rejected_task1.51/17/2018 15:32
5226932workflow_started1.51/17/2018 15:44
5226933workflow_acquired_task1.51/18/2018 12:36
5226934workflow_signoff1.51/18/2018 12:36
5226935workflow_forwarded_task1.51/18/2018 12:36
5226936workflow_acquired_task1.51/18/2018 13:12
5226937workflow_aborted1.51/24/2018 20:21
5226938workflow_started1.51/24/2018 21:39
5226939workflow_acquired_task1.51/26/2018 7:58
5226941workflow_signoff1.51/26/2018 7:59
5226940workflow_forwarded_task1.51/26/2018 7:59
5226942workflow_acquired_task1.51/26/2018 12:27
5226943workflow_signoff1.52/6/2018 18:25
5226944workflow_forwarded_task1.52/6/2018 18:25
5226945workflow_acquired_task1.52/7/2018 16:35
5226946workflow_forwarded_task1.52/7/2018 16:36




KL001_2-1643305156036.png

 

 

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"
)

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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"
    )

 

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

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
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




KL001
Frequent Visitor

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. 
KL001_0-1643402016147.pngKL001_1-1643402255018.png

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!