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:
Solved! Go to 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])))
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.
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.
I currently have this but it doesn't work:
SELECT(DSO for all divisions[Division],
AND(
[Date] = TODAY(),
MIN(DSO for all divisions[DSO])
)
)
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])))
Thank you very much this DID work. Thanks again!
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |