Why would a simple filter not work?

[plew]

I can't understand how something as basic a filtering go this wrong, but it apparently doesn't work in my current case. Can someone see a reason? 

I filter Table visual by "Category ID" field. I want to unfilter several Category IDs from the data. When I unselect the first one, it disappears as it should. 

However, when I unselect a second value, the filter stops working. Values unselected in the filter appear in the table, as seen below:

Interestingly, when I invert the filter and select one or many of Category IDs, the table returns empty.

I tried using the "Category ID" as a proper dimension in star-schema model and as an element of the fact table - neither has worked.
The other column is a DAX measure "Value" - a simple SUM of amounts from fact table.

[v-karpurapud]

Hi

[v-karpurapud]

Hi @plew 

We have not yet heard back from you about whether our response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud]

Hi @plew 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @plew 

We have not yet received a response from you regarding your query if you find the response helpful, kindly mark it as the accepted solution and provide kudos, as this will aid other members with similar queries.

Thank You.

[v-karpurapud]

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.

[plew]

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. 

[v-karpurapud · Accepted solution]

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.

[PijushRoy]

Hi @plew 
Please follow the steps mentioned by @Elena_Kalina 
If not solved, please check your data model.
Please share PBIX file google drive/dropbox link with sample data only

[Elena_Kalina]

Hi @plew 

The Category IDs appear to be very large numbers (like 79469421976404930). These might be getting treated as text rather than numbers.

You should

• Check the data type of your Category_ID column in Power Query

• Ensure it's set consistently (all as text or all as numbers)

• If using text, ensure there are no hidden spaces or characters

[plew]

I had the Category_ID formatted as Whole Numbers for both tables (dimension and fact). I have also tried with General formatting and it hasn't worked either. 
I can't seem to pick Text format for this column - not exactly sure why, but it may be because I'm using data in DirectQuery mode. 
I've double checked that there are no hidden spaces and characters in the Category_ID values.