DAX measure to filter content based on multiple other values in table

[ems]

Hi all,

I am trying to find a way to write a measure that helps classify "cases" in a table of data as "open" or "closed."

The table of data is inherited from another source and I don't have control over its formatting, and am being strongly discouraged from messing with the existing data structure it's a part of (including making a custom table or column), hence trying to write a measure; the way the table is laid out isn't ideal and I'm trying to work around it.

Relevant aspects of the table:

Each row indicates an instance of a type of document, of which there are many, but the most relevant information here is that there are documents used to "close" a case, HOWEVER, a case may then be re-opened.

Rows contain (again, only listing what's relevant):

-case "ID" (unique identifier)

-Type of document

-date of document

For each ID, there will be many rows/many kinds of docments.

SAMPLE DATA (again, only relevant columns shown)- imagine "openingdoc" opens a case, "continuingdoc" is a document that gets done while a case is open, and CLOSING doc is a document which closes a case:

ID	Doctype	Docdate
ID001	openingdoc	1/1/20
ID001	continuingdoc	2/1/20
ID002	openingdoc	1/1/20
ID002	CLOSING doc	2/2/20
ID001	CLOSING doc	2/3/20
ID003	openingdoc	3/1/20
ID001	openingdoc	3/2/20

What I basically need to find a way to get is, for each unique ID, is the most recent document a "close" document, or literally any other kind of document (which would indicate that they're open).  Best way I can think to get this information from DAX is to make it generate a custom table with one row being the unique IDs and the other being a generated row that indicates either "open" or "closed" but I am open to ideas on better ways to try to format the output.  Right now I'd be happy with any help or guidance.  New enough to DAX that I'm struggling to visualize how it iterates in a way that lets me imagine a good solution to this problem, so this may be much simpler to folks who are used to this language than I'm realizing it is (my instinct is to make a series of nested loops, but how to do that in DAX- or if I even should- is escaping me.)  I have not been able to find quite this problem in searching common problems or help articles despite spendig a few hours trying to do so, but that may be that I'm not conceptualizing it correctly.

So, desired output:

ID	open or closed?
ID001	open
ID002	closed
ID003	open

<edited for clarity 12/29 1:23pm>

[amitchandak]

@ems , try

New measure

if(isblank(countx(filter(Table, Table[Doctype] = "CLOSING doc"),[ID])), "Open", "closed")

or a new column

if(isblank(countx(filter(Table, [[ID] = earlier([ID]) && Table[Doctype] = "CLOSING doc"),[ID])), "Open", "closed")

[amitchandak]

@ems , How based on the Date column, you can decide, which row needs to be considered open and which one for close?

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

[ems]

@amitchandak updated to add tables.

[Greg_Deckler]

@ems Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ems]

@Greg_Deckler updated to add tables.