Problem displaying data

[jklop195]

Hi, I have this data and I'm having trouble displaying the case number.

I want to display the oldest assigned date and case number based on the "pending" status. I used a formula to display the oldest date with pending status but I'm having problem displaying the case number. 

Example: I can see that the oldest case date with pending status is 6/17/19 but can't display the case number in this example being (3456-17)

Case Number	Date Assigned	Status
3456-17	6/17/19	Pending
4542-29	6/29/19	Pending
6209-24	5/24/19	Closed
6806-29	4/29/19	Closed
7312-04	4/4/19	Closed
1209-09	6/9/19	Closed
8765-05	7/5/19	Closed

[Greg_Deckler · Accepted solution]

So, you have something like this measure?

Oldest Pending Date = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])

If you display that in a table along with this measure you should get what you need:

Oldest Case Number = 
VAR __min = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])
RETURN
MAXX(FILTER('Cases',[Date Assigned] = __min && [Status] = "Pending"),[Case Number])

[Greg_Deckler · Accepted solution]

So, you have something like this measure?

Oldest Pending Date = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])

If you display that in a table along with this measure you should get what you need:

Oldest Case Number = 
VAR __min = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])
RETURN
MAXX(FILTER('Cases',[Date Assigned] = __min && [Status] = "Pending"),[Case Number])

[jklop195]

---

@Greg_Deckler wrote:

So, you have something like this measure?

Oldest Pending Date = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])

If you display that in a table along with this measure you should get what you need:

Oldest Case Number = 
VAR __min = MINX(FILTER('Cases',[Status] = "Pending"),[Date Assigned])
RETURN
MAXX(FILTER('Cases',[Date Assigned] = __min && [Status] = "Pending"),[Case Number])

---

hi,

I used this measure to get the oldest case;

Oldest Case = CALCULATE(MIN('Data'[Assigned Date]),'Data'[Status]<>"Resolved")

---

I tried what you suggested and it return with a syntax error.

Oldest Case Number = (DAX(var __min=minx(filter('Data',[Claim Status]<> [Resolved]),[Assigned Date])))

[v-piga-msft · Accepted solution]

Hi @jklop195 ,

By my tests, the formulas from Greg_Deckler should be helpful.

It seems that your formula is not similar to Greg_Deckler's and has syntax error.

Normally, if you use var function, we need use return to get the value.

If you still need help, please share your sample pbix so that we could help further on it.

You also could refer to my test pbix which has been attached.

Best  Regards,

Cherry