cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Microsoft Employee

Hi @Anonymous,

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
Microsoft Employee

Hi @Anonymous,

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.
Microsoft Employee

Hi @Anonymous,

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.
Super User

Hi @Anonymous

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