Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
DATETIME | PRODUCT_NO | TRANSACTION_NO | TRANSACTION_NAME |
2022-09-01 09:00:00 | AAA111 | 1 | GRINDING |
2022-09-01 09:03:00 | AAA111 | 2 | POLISHING |
2022-09-01 09:04:00 | BBB222 | 1 | BENDING |
2022-09-01 09:10:00 | AAA111 | 3 | GRINDING |
2022-09-01 09:14:00 | CCC333 | 1 | DRILLING |
2022-09-01 09:04:00 | BBB222 | 2 | PACKING |
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!
@jknottUOW I hope this helps you. Thank You.
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |