Table visual displaying zero (nil) values even when filtered value Is not 0.

[DazBC]

I have a table visual that is displaying nil values even when the sum of amount is filtered to 0. I have reduced my data model to one table with the following headings Accounting Date, AccID, Control No and Amount. I am trying to filter the on AccID (one account number 03/01|130) and set the sum of Value filter to Is not 0.  
I duplicated the data set in Power Query and filtered all AccID out except for 03/01|130. When I do this and set the amount filter to Is not zero the visual displays correctly. 

Image with all AccID included 

Image with only AccID 03/01|130

I tried copying the pbix file across but says it is not supported (my first time using this forum).

[HotChilli]

Thanks for the pbix.

I don't have time to give a full response but, initially, when I see these types of issues, I immediately think of the precision of decimal numbers and how they look in a visual compared to what their 'actual' values are.  0 is not always 0.00, sometimes it is 0.00000012, for example. (0.0000012 is not 0 hence the rows are not filtered out)

That is the reason the visual is showing you rows with 0 in them (you can prove that by increasing the number of decimal places (click on the amount field, go to the 'formatting' bar on the ribbon, it'll be set to Auto so put it to 8 or something and the values will appear).  <- edit (it actually has to be around 13 places)

So, you can fix it by using a currency / fixed decimal data type which will limit to 2 decimals and won't contain the tiny values.

--

I can have a look at the other visual later.  I expanded the decimal places and the values are coming through as 0.0000000 (which is equal to zero, so the rows WILL be filtered out).  So there will be a difference in the two queries which I don't have time to look at right now

[DazBC · Accepted solution]

I managed to resolve it (with a little help from ChatGPT), thank you HotChilli for putting me on the right path, much appreciated.

In Power Query I change the type in my model to : 

= Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", Currency.Type}})

[DazBC]

Thanks HotChilli. Yes, you are correct that it looks like a rounding issue and there are values up to 16 decimal places. 

However, I tried formatting this to a decimal value (2 decimals, Currency and a whole number) and it still does not exclude the zero values as suggested. Is there someting I should be doing in Power Query?

Appreciate the feedback.

[HotChilli]

Post the pbix on a 3rd party site (a legitimate one please) and put the link here.  I'll have a look.

[DazBC]

Thank you: Here you go  Test Table.pbix 

[gmsamborn]

Hi @DazBC 

Upload your pbix to OneDrive, GoogleDrive, WeTransfer, DropBox, etc and share the link (with permissions depending on platform).

[DazBC]

Thank you: Here you go  Test Table.pbix