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

[Insert_Key]

Hi everyone

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 trialling function after function after function for far too long and am super confused now I would really appreciate some help!

Most of my attempts at developing a measure result with an error message advising something along the lines of '...multiple values have been supplied where a single value was expected', but by now I have failed in dozens of different and exciting ways. I've knocked up a file with dummy data and measures for reference - hopefully this helps with understanding my request and developing a solution. The purpose of the pivot table I'm working with is to generate statistics that are consumed and presented as part of a dynamic and interactive 'dashboard' on another sheet; the pink values are measures that I am yet to finalise and of them, it is the first "SYS" value that I am trying to populate with this measure. Once I know the approach for that statistic, I'll be able to apply it to the other "SYS" metrics.

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!

 

[Insert_Key]

Solution provided in another forum; providing the code in case anyone lands here after searching for help with a similar problem:

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

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Re: Measure that filters a table to a single row and returns text from a specified column (Excel365)
and DAX measure that filters a table to a single row and returns text from a specified column

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Insert_Key]

Thanks, Fluff, and apologies - requirement noted for the future. There are no additional sites/posts to report.