Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.