Forum Discussion
Why would a simple filter not work?
Hi plew
Thank you for the update. Since access to the underlying data model is limited and the report is operating in DirectQuery mode, a practical approach would be to create a calculated column within Power BI to convert the CATEGORY_ID into text. This method helps to avoid floating-point issues without requiring any changes to the data source. Once the calculated column is added, use this text-based column, such as CATEGORY_ID_TEXT, in all slicers, filters, and visuals throughout the report. By using a text column for filtering, the report ensures consistent and accurate results, as text comparisons are not affected by numeric precision limitations.
Thank You.
Hi plew
Welcome to the Microsoft Fabric Community Forum.
This issue is caused by using very large integer values in the CATEGORY_ID field, which go beyond the 15–16 digit limit. In Power BI, especially in DirectQuery mode, these large numbers can run into floating-point precision issues because Power BI uses double-precision floating-point format for some numeric operations. As a result, integers of this size may not be represented accurately, and filters like “is not” may not work as intended, causing excluded values to still show up in your visuals.
To fix this, it is recommended to convert the CATEGORY_ID field to text. If you can change the source SQL database, update the query to cast CATEGORY_ID as a VARCHAR so it imports as text in Power BI. If you can’t modify the source, you can create a calculated column in Power BI using the DAX formula CATEGORY_ID_TEXT = FORMAT([CATEGORY_ID], "0"). Then, use this text column in all your slicers, filters, and visuals. Since text fields aren’t affected by floating-point precision, this will help ensure your filters work consistently throughout the report.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
Thank you for the advice.
In my scenario I'm using data in DirectQuery mode. Not being an owner of the source model, Power BI doesn't let me see the original Data Type. I've turned to the data engineers in my organization, but still haven't got an answer, so I cannot yet determine if changing the underlying data type (not just format) is going to solve this.
Hi plew
Thank you for the update. Since access to the underlying data model is limited and the report is operating in DirectQuery mode, a practical approach would be to create a calculated column within Power BI to convert the CATEGORY_ID into text. This method helps to avoid floating-point issues without requiring any changes to the data source. Once the calculated column is added, use this text-based column, such as CATEGORY_ID_TEXT, in all slicers, filters, and visuals throughout the report. By using a text column for filtering, the report ensures consistent and accurate results, as text comparisons are not affected by numeric precision limitations.
Thank You.
