Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
v-jiascu-msft
Microsoft Employee
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 )

Filter by number of decimal places .jpg

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft Employee
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.
v-jiascu-msft
Microsoft Employee
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 )

Filter by number of decimal places .jpg

 

 

 

 

 

 

 

 

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors