Forum Discussion
Get all values between 2 strings
Hi,
I'm trying to get all values between two string values. For example, from 1A00000 to 2Z99999. How can i do it?
I have this table:
| FROM | TO | VALUE |
| 1A00000 | 2Z99999 | Value 1 |
| 3A00000 | 7Z99999 | Value 2 |
| 8A00000 | 8Z99999 | Value 3 |
| 9A00000 | 9Z99999 | Value 4 |
I need a filter to check if i ask for register "1B45877" returns Value 1. If i ask for "8G74584" returns Value 3...
If the table provided is TableK and these values 1B45877, 8G74584 are in a table (TableP) - which is not connected with a relationship -
and are then pulled in to a table visual, you can create a measure :
MeasureA = VAR _PVal = MIN(TableP[Column1]) RETURN CALCULATE(MIN(TableK[VALUE]), TableK[FROM] <= _PVal && TableK[TO] >= _PVal)This will require testing because it relies on < , > working for the alphabetical comparison so I suggest testing edge cases. In my head it feels OK but I may be wrong.
7 Replies
marrasate , Create a measure and plot it with the required column
countrows(filter(Table, Table[column] >= '1A00000' and Table[column]<= '2Z99999' ))
Are you trying to generate the values 1A00000, 1A00001, 1A00002........ in a column?
Im trying to do this:
FROM TO VALUE 1A00000 2Z99999 Value 1 3A00000 7Z99999 Value 2 8A00000 8Z99999 Value 3 9A00000 9Z99999 Value 4 If you want a good answer you'll have to be clearer about:
a) what you have to start with
b) what you want to end with
c) an explanation of how to get from a) to b)
I have this table:
FROM TO VALUE 1A00000 2Z99999 Value 1 3A00000 7Z99999 Value 2 8A00000 8Z99999 Value 3 9A00000 9Z99999 Value 4 I need a filter to check if i ask for register "1B45877" returns Value 1. If i ask for "8G74584" returns Value 3...
If the table provided is TableK and these values 1B45877, 8G74584 are in a table (TableP) - which is not connected with a relationship -
and are then pulled in to a table visual, you can create a measure :
MeasureA = VAR _PVal = MIN(TableP[Column1]) RETURN CALCULATE(MIN(TableK[VALUE]), TableK[FROM] <= _PVal && TableK[TO] >= _PVal)This will require testing because it relies on < , > working for the alphabetical comparison so I suggest testing edge cases. In my head it feels OK but I may be wrong.
Hi, marrasate ;
As @HotChilli said , you could use measure as follow:
Measure = CALCULATE(MAX('Table'[VALUE]),FILTER('Table',[FROM]< MAX('register'[register])&&[TO]>MAX('register'[register])))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
