Forum Discussion

plew's avatar
plew
Frequent Visitor
1 year ago
Solved

Why would a simple filter not work?

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 wan...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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.

Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

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's avatar
    plew
    Frequent Visitor
    1 year ago

    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.