Forum Discussion

marrasate's avatar
marrasate
Regular Visitor
4 years ago
Solved

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 3A000...
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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.