Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jknottUOW
Frequent Visitor

DAX to extract column value from transaction list table based on slicer filter and latest datetime

I have a large table consisting of transaction records for a production process, with each row containing information about the product, time, and process - e.g.

DATETIMEPRODUCT_NOTRANSACTION_NOTRANSACTION_NAME
2022-09-01 09:00:00AAA1111GRINDING
2022-09-01 09:03:00AAA1112POLISHING
2022-09-01 09:04:00BBB2221BENDING
2022-09-01 09:10:00AAA1113GRINDING
2022-09-01 09:14:00CCC3331DRILLING
2022-09-01 09:04:00BBB2222PACKING

 

I currently have a report page where I have a slicer that filters based on 'PRODUCT_NO', so I am able to see the transactions for e.g. just 'AAA111' in a table.

What I am trying to do is have a single-value card that shows the latest/last TRANSACTION_NAME for the product currently selected by the slicer, i.e. when I select 'AAA111' in the slicer, I want the single value card to read 'GRINDING'; selecting 'BBB222' would give 'PACKING'; etc.

I have tried to achieve this by making a new measure:

 

selectedProductLastEvent = 
VAR productEvents = CALCULATETABLE(
    FILTER(TRANSACTIONS, TRANSACTIONS[PRODUCT_NO]=[SELECTEDVALUE(TRANSACTIONS[PRODUCT_NO])
)
VAR latestEvent = CALCULATE(
    productEvent[TRANSACTION_NAME],
    FILTER(productEvents, MAX(productEvents[TRANSACTION_NO]))
)
RETURN
    latestEvent

 

and a few variations on this theme, but I'm not having any luck. The current error I'm getting is

 

Table variable 'productEvents' cannot be used in current context because a base table is expected.

 

but I believe i'm also having an issue with trying to reference the table column 'productEvent[TRANSACTION_NAME]' in this fashion. 

 

I have structured my measure as intuitively I am trying to filter a sub-table from the entire TRANSCATIONS table that just contains the transactions for the selected PRODUCT_NO, then find the max (or latest datetime) transaction from that sub-table. If there is a better way to structure this, I'm certainly open to all ideas on that too. 

 

Any help on how to achieve my aim would be greatly appreciated!

1 REPLY 1
Mahesh0016
Super User
Super User

@jknottUOW I hope this helps you. Thank You.

Mahesh0016_0-1693201726826.png

Mahesh0016_1-1693201868757.png

Mahesh0016_2-1693201887487.png

 

latest Tran Name =
IF (
MIN ( 'Table Transaction'[DATETIME] ) = [First Date],
CALCULATE (
MIN ( 'Table Transaction'[TRANSACTION_NAME] ),
'Table Transaction'[PRODUCT_NO]
= SELECTEDVALUE ( 'Table Transaction'[PRODUCT_NO] ),
FILTER (
ALLSELECTED ( 'Table Transaction'[TRANSACTION_NO] ),
'Table Transaction'[TRANSACTION_NO]
= CALCULATE (
MAX ( 'Table Transaction'[TRANSACTION_NO] ),
ALLEXCEPT ( 'Table Transaction', 'Table Transaction'[PRODUCT_NO] )
)
)
),
CALCULATE (
MIN ( 'Table Transaction'[TRANSACTION_NAME] ),
'Table Transaction'[PRODUCT_NO]
= SELECTEDVALUE ( 'Table Transaction'[PRODUCT_NO] ),
FILTER (
ALLSELECTED ( 'Table Transaction'[TRANSACTION_NO] ),
'Table Transaction'[TRANSACTION_NO]
= CALCULATE (
MIN ( 'Table Transaction'[TRANSACTION_NO] ),
ALLEXCEPT ( 'Table Transaction', 'Table Transaction'[PRODUCT_NO] )
)
)
)
)

@jknottUOW  Please validate and share your views. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.