Measure that filters a table to a single row and returns text from a specified column (Excel365)

[Insert_Key]

Hi there

I still have a lot to learn about DAX, and have become stuck trying to develop a measure that will filter down my source table to a single row by using values in two columns and return the (text) value from a different, specified column.

I have two measures that filter a table and calculate the expected values for (a) the oldest date for an unresolved service ticket and (b) the corresponding number of days elapsed after that date, but what I am unable to do is to retrieve the text value from a column for the row that table is calculating the age and date from. I've been going around and around in circles researching and trying different functions for far too long and am super confised now. I would really appreciate some help! 🙂

Most of my attempts at developing a measure result with an error message advising ~ that multiple have been supplied where a single value was expected, but by now I have failed in dozens of different ways. I've knocked up a file with dummy data and measures for reference - hope this helps with understanding my request and developing a solution. The pivot table I'm working with is used to generate statistics that are consolidated and presented as part of a dynamic and interactive 'dashboard' on another sheet; it is the "SYS" values that I am trying to populate with this measure:

The image below is from the sample file provided, with the column in pink manually created to illustrate the desired result.

Thanks in advance for your help!🤗

[ThxAlot · Accepted solution]

Oldest Unresolved Ticket_Days :=
VAR EndDate =
    DATE( 2024, 07, 31 )
RETURN
    CALCULATE(
        DATEDIFF(MIN( DummyData[Created] ), EndDate, DAY),
        ISBLANK( DummyData[Resolved] )
    )

Oldest Unresolved Ticket_Date := CALCULATE( MIN( DummyData[Created] ), ISBLANK( DummyData[Resolved] ) )

Sys \w oldest ticket :=
MAXX(
    TOPN(
        1,
        CALCULATETABLE( DummyData, ISBLANK( DummyData[Resolved] ) ),
        DummyData[Created], ASC
    ),
    DummyData[Business System]
)

[Insert_Key]

Amazing! I took it over to my actual file, inserted under an existing short conditional statement (IF([Oldest Unresolved Ticket]=0,"No unresolved tickets",) swapped out the table & column references, pressed enter and it worked flawlessly - exactly what I wanted! 🤗🎉 

I had tried TOPN numerous times, but just couldn't figure out how to get it to do what I wanted. Thank you so much.

[some_bih]

Hi @Insert_Key you want solution in Excel? If yes, in which view - like on sheet Output, your pinky column?

Check picture questions in yellow for your VAR. 

You will always change VAR value or ...

[Insert_Key]

Thanks for your reply 😊

1. Yes, solution in Excel please

2. Yes, I want the result to look like the pink mock-up - identifying the oldest system per row, whether the group is expanded or collapsed (illustrated below). This allows me to pick values from different levels.

My real file is much more complex, the sample file doesn't have the additional tables and the queries associated with them. The end date - that I have inserted as VAR - will change dynamically from month to month, yes.

I was having loads of problems getting some of my measures to work yesterday. I thought it was caused by setting up filters incorrectly, but after far too long I realised that the issue was that I was using MIN or other timedate functions within the measure, and that they were incompatible with its structure. If I inserted the date explicitly, they'd work. This is how I discovered VAR yesterday! 😲

I felt that using a static date value in the sample could lead to solutions that would work fine with the dummy data yet fail with the real data, so tried to provide something that would better emulate the setup of my real file without needing to build additional tables/queries. I've just checked the real file, and neither of the two measures in question needed reworking with VAR - they both contain MIN and/or MAX functions. I have provided one below for reference.

Oldest Unresolved Ticket:=CALCULATE(DATEDIFF(MIN('Jira_Data'[Date Created]),MAX('Dates in Reporting Period'[Dates in Reporting Period]),DAY), KEEPFILTERS(ISBLANK('Jira_Data'[Date Resolved])))