how to apply range between string type data

[Harish85]

Hi All,

I want to apply between or range between the data (C240000001 to C246000216) as there is more than 250000 data, I want to apply range and pull the data.
How to apply range for above values.

[v-sgandrathi]

Hi @Harish85,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you for your cooperation. Have a great day.

[v-sgandrathi]

Hi @Harish85,

Thank you @Olufemi7 and @Mauro89 for your respponse.

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[Olufemi7 · Accepted solution]

Hello @Harish85, 

Power BI treats IDs like C240000001 as text, so you can’t use a Between slicer directly.

To apply a numeric range filter, you need to extract the numeric portion and convert it to a number.

Option 1: Power Query (Pre-filter before loading)

• Go to Transform Data.

• Add a Custom Column: 
  
  

Number.FromText(Text.Middle([ID], 1, Text.Length([ID]) - 1))

• Rename the column to ID_Number.

• Apply a filter:

  • ID_Number >= 240000001

  • ID_Number <= 246000216

• Load the filtered data into your report.

This is best for large datasets because it filters before import.

Option 2: DAX Calculated Column (Interactive filtering)

• In your report view, create a new column:
  
  

ID_Num = VALUE(MID([ID], 2, LEN([ID]) - 1))

• Add a Slicer visual.

• Drag ID_Num into the slicer.

• Change slicer type to Between.

• Set the range: 240000001 to 246000216.
  
  

This is best for keeping all data and filtering interactively.

Column Naming Convention (Power BI)

To avoid confusion when using both Power Query and DAX in the same report, I recommend this naming:

• Power Query → ID_Number (created during data load; used for filtering before import)

• DAX → ID_Num (created in report layer; used for interactive filtering)
  
  

Outcome Your slicer will show a horizontal slider with two handles, allowing users to filter between any numeric range of IDs.

This works perfectly with either ID_Number (Power Query) or ID_Num (DAX).

This approach ensures you can handle 250k+ rows efficiently while keeping your report interactive and professional.

Table VisualBetween-Styled Slicer for ID_NumberBetween-Styled Slicer for ID_Num

[v-sgandrathi]

Hi @Harish85,

The Between filter doesn’t work on your column because it only works with numbers or dates, and your IDs are stored as text, like C240000001. To filter a range such as C240000001 - C246000216, you can create a new column that takes out the text part and keeps only the number. For example, you can use a DAX formula like 'ID_Number = VALUE(SUBSTITUTE([YourID], "C", ""))' and adjust it if the prefix is different. Once this new column is a number, you can use it in a 'Between' slicer or range filter, and it will correctly filter your original IDs. This way, you can still show the original IDs but filter them using a numeric range.

Thank you.

[Mauro89]

Hi @Harish85,

if I got you right that you want to do some kind of filtering with the "between" function, then I would do the following based on your screenshot.

Between filtering is only possible with number typed data. Thats why I would extract the numbers from the column and create the filter or selection based on the numbers. There seems to be a logic within the numbers as of your screenshot showing increasing numbers.

Hope this helps!

Best regards!

PS: if this solves your issue or you like it, leave some kudos or mark it as solution.