Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Proud to be a 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]
)
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.
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |