cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Filter by number of decimal places

I have a large table with a number of values in a field that have 3-6 decimal places - I would like to see/extract these rows only. Is there a way I can do this using filters in Power BI Desktop?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Filter by number of decimal places

Hi @olivia1234,

Maybe you could try this formula as a calculated column. Please test it before using it.

```Decimal3to6 =
VAR NumOfDecimal =
IF (
[Column1] = TRUNC ( [Column1] ),
0,
LEN ( [Column1] ) - LEN ( TRUNC ( [Column1] ) )
- 1
)
RETURN
IF ( NumOfDecimal >= 3 && NumOfDecimal <= 6, 1, 0 )```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Super User IV

## Re: Filter by number of decimal places

The way I would go about it is to go into the Query Editor and do the following:

• Duplicate the column that has got the decimal places.
• Then split this column by the full stop "." delimiter
• Make sure that the data type for this column is set to Text.
• I would then transform this column to Length (This will give you the amount of chacters in each Column).
• I would then create a conditional column where it would say where the Column is < 4 then "Less than 3" else "Between 3 and 6"
• Then delete all the columns that were created except for this last Conditional column.
• Then Close and Apply your data.

Then in your report view you could use this as filter or slicer in your data.

Proud to be a Super User!

Power BI Blog

Highlighted
Microsoft

## Re: Filter by number of decimal places

Hi @olivia1234,

Maybe you could try this formula as a calculated column. Please test it before using it.

```Decimal3to6 =
VAR NumOfDecimal =
IF (
[Column1] = TRUNC ( [Column1] ),
0,
LEN ( [Column1] ) - LEN ( TRUNC ( [Column1] ) )
- 1
)
RETURN
IF ( NumOfDecimal >= 3 && NumOfDecimal <= 6, 1, 0 )```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft

## Re: Filter by number of decimal places

Hi @olivia1234,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021