Hello,
is it possible to find the last entry in a table where a column has a certain prefix?
e.g.
if I have rows with the following values for a certain column 1.1, 1.2, 2.1, 3.1, 1.3, 3.2., I would like to be able to find the last row beginning with a 1, or a 2 or a 3.
i.e. if I look for 1, the answer would be 1.3, if I look for 2, the answer would be 2.1, and if I look for 3 the answer would be 3.2.
Hopefully Iโve explained it ok.
Thanks for any help.
Steve
Solved! Go to Solution.
What youโre trying to do is hugely expensive and inefficient. You should consider a different numbering system. At the very least, make the number column type Decimal rather than Text, then itโs as simple as:
MAX(
SELECT(
MyTable[CertainColumn],
AND(
([CertainColumn] >= 2.0)
([CertainColumn] < 3.0)
)
)
)
I believe some more details could help the community members to assist in a better manner.
Could you please mention where and how you would like to capture that indicator of last entry? In another column?
Also if it is in another column would you like three statuses to be captured on that indicator columm? For example row where 1.3 value is there,the indicator says โLast entry for 1 Seriesโ the rowwhere 2.1 is there, the indicator says โLast Entry for 2 Seriesโ and so on.
Is last entry in terms of maximum value for that series , meaning are the values in a series always increasing when rows are added or are they random in entry orderand still last entry means maximum value.
Hello @Suvrutt_Gurjar,
thanks for you reply.
I would like to put this expression in a text column in a separate table. I will only ever use one at a time, but I will put them in a switch statement which will be dependent on a drop down menu option. Rows for a series are always in increasing value. e.g. 1.7 will always be in a row after 1.6 (but not always consecutive)
I have managed to write the following expression which seems to return the list I am looking for (I am still working on it so Iโm not 100% sure) i.e. it filters out all the columns that donโt have prefix โ1.โ. I am still working out how to get the last entry in the list though.
SELECT(Table Name[Column], LEFT([Column],2)=โ1.โ)
Thanks,
Steve
Thank you for the updates. I believe you are proceeding correctly. If you are looking for maximum value of that column then , I believe you may wrap your SELECT statment with a MAX() function.
If you are looking for the key value of the row with the maximum value in that series column, then I believe you may wish to take a look at MAXROW() function.
Hi @Suvrutt_Gurjar,
thanks for the tips.
I wasnโt able to use MAX as if I treat the values as numbers then 1.1 and 1.10 will be the same value. So I had to use text and use the following expression
INDEX(
SELECT(TableName[Column], LEFT([Column],2)=โ1.โ),
COUNT(SELECT(TableName[Column], LEFT([Column],2)=โ1.โ))
)
Is there a better way of getting the last entry in a list, rather than running the same expression twice i.e. once to get values, and 2nd time to count values.
Thanks,
Steve
What youโre trying to do is hugely expensive and inefficient. You should consider a different numbering system. At the very least, make the number column type Decimal rather than Text, then itโs as simple as:
MAX(
SELECT(
MyTable[CertainColumn],
AND(
([CertainColumn] >= 2.0)
([CertainColumn] < 3.0)
)
)
)
Another option, along the same line as my last suggestion, but to get the row itself with the highest value:
ANY(
ORDERBY(
FILTER(
"MyTable",
AND(
(DECIMAL([CertainColumn]) >= 2.0)
(DECIMAL([CertainColumn]) < 3.0)
)
),
DECIMAL([CertainColumn]),
TRUE
)
)
This assumes CertainColumn is of type Text. FILTER() gathers rows within the desired range. ORDERBY() orders the resulting rows by their CertainColumn values, highest-to-lowest. ANY() returns the first of the sorted rowsโthe row with the highest CertainColumn value within the desired range. If CertainColumn is of type Decimal (as I suggested previously), remove the DECIMAL() references in the above.
Hi @Steve,
thanks for youโre suggestion. Iโve changed my numbering system based on your advice. Iโm using the ranges 1000, 2000, 3000 etc now, as I donโt expect these ranges to be fully used.
Thanks.
Steve
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |