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 have a list of tickets but there is a row for each "touch" on the ticket. In my example below the first row from 1/3/2022 8:59AM was the original request on the ticket and the last entry is our response back (on 1/4/2023). I'm trying to just show two columns in a visual with original and response (see expected result below). Any thoughts?
Anticipated Outcome:
You can do one of these two ways. Add these measures:
1st method:
First Record (Original) =
VAR _Firstdate = MIN ( 'Table'[Date] )
RETURN
CALCULATE (
MAX ( 'Table'[Notes] ),
FILTER ( 'Table', 'Table'[Date] = _Firstdate)
)
Last Record (Response) =
VAR _Lastdate = MAX ( 'Table'[Date] )
RETURN
CALCULATE (
MAX ( 'Table'[Notes] ),
FILTER ( 'Table', 'Table'[Date] = _Lastdate)
)
2nd method:
Original =
var _s = SELECTEDVALUE('Table'[Ticket Number])
var _first = CALCULATE( Min ('Table'[Date]), 'Table'[Ticket Number] = _s )
return if ( ISFILTERED('Table'[Ticket Number]), CALCULATE( MAX('Table'[Notes]), FILTER('Table', 'Table'[Date] = _first)))
Response =
var _s = SELECTEDVALUE('Table'[Ticket Number])
var _first = CALCULATE( Max ('Table'[Date]), 'Table'[Ticket Number] = _s )
return if ( ISFILTERED('Table'[Ticket Number]), CALCULATE( MAX('Table'[Notes]), FILTER('Table', 'Table'[Date] = _first)) )
As an enhancement, if you have thousands of records, I recommend you mark these calculation in doing power query in another column and then use that as filter.
= = = = = = = = = = = = = =
Create Visual with
Ticket Number and rename as Ticker ID
Use these two measures you have created.
Hope this helps!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |