Expression needed to find lowest [DSO]but show the corresponding [Division] based on [Date]= Today()

[Tiger1]

I need an expression to find the lowest [DSO] if [date] = TODAY().  I want to show the corresponding [Division] of that lowest [DSO] value.  Here is my table:

[Suvrutt_Gurjar]

There could be some approaches. One could be based on MINROW() expression.

The other is as follows

Please create a slice  called say "Todays Records" on the table  with a slice filter expression such as [Date]=TODAY()

Then the expression for the Division with the lowest DSO value for TODAY() can be

ANY(SELECT( Todays Records[Division], [DSO]=   INDEX(SORT(Todays Records[DSO], FALSE),1)))

Where 'Todays Records" is the slice with [Date]=TODAY() expression.

Not tested. Please test well.

[Tiger1]

Thank you.  I wanted this in one expression - so no slices.   Reason - I am trying to place it in one of my Word doc templates.

[Tiger1]

I currently have this but it doesn't work:

SELECT(DSO for all divisions[Division],
AND(
[Date] = TODAY(),
MIN(DSO for all divisions[DSO])
)
)

[Suvrutt_Gurjar · Accepted solution]

Thank you for details.

The basic syntax issue with your expression is you cannot simply mention MIN(DSO for all divisions[DSO])in the AND() expression. It will need to be something like [DSO] =MIN(DSO for all divisions[DSO]) because AND() needs subexpressions to evaluate as TRUE or FALSE.

Second syntax issue is select() will return a list, so the expression needs to be wrapped with ANY() to get a single value.

The third and major logical issue with your expression is the part of the expression MIN(DSO for all divisions[DSO]) will give the minimum DSO value for the entire table/slice , meaning for all dates and not just today's date. That is why I requested you to create a slice for [Date]= TODAY().

So your expression could be modified to the following expression 

ANY(SELECT(DSO for all divisions[Division],
AND(
[Date] = TODAY(),
[DSO]=MIN(SELECT(DSO for all divisions[DSO], [Date]=TODAY()))
)
))

However, even though the above expression will work,  it has a SELECT() in another SELECT() that could be sync time / evaluation time expensive. If the table size is not very large , you could go for it.

You may want to try my suggested slice based approach. I have tested that it works. Even if you are using the expression in a template, slices can be used in a template.

Edit:

---

@Suvrutt_Gurjar wrote:

ANY(SELECT( Todays Records[Division], [DSO]=   INDEX(SORT(Todays Records[DSO], FALSE),1)))

---

Also my above suggested expression can be further simplified as follows 🙂.  Sorry for sharing a bit longish expression earlier.

ANY(SELECT( Todays Records[Division], [DSO]=   MIN (Todays Records[DSO])))

[Tiger1]

Thank you very much this DID work.  Thanks again!