Forum Discussion

SHullen's avatar
SHullen
Frequent Visitor
3 months ago

Numeric Between Slicer Not Showing Actual Values

Hi.  I created a new column called NetProfit_Filter in Transform Data.  The column rounds net profit to the nearest 1,000.  When I use the column in a between slicer, the rounded numbers aren't showing up.  Makes no sense.

 

= Table.AddColumn(#"Renamed Columns", "NetProfit_Filter", each Number.Round([Net Profit], -3, RoundingMode.AwayFromZero))

 

  

 

$24,414 is obviously not rounded to the nearest $1,000.

 

Thanks,

 

 

Need Help

5 Replies

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi SHullen ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.


    pcoley & oussamahaimoud ,Thanks for your prompt response

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • oussamahaimoud's avatar
    oussamahaimoud
    Icon for Memorable Member rankMemorable Member
    3 months ago

    Hi SHullen,

    Hope you are doing well!

     

    Use a DAX Calculated Column Instead

    Delete the Power Query column and create it in DAX:

     

    NetProfit_Filter = 

    MROUND([Net Profit], 1000)

     

    Hope this helps! Feel free to ask more questions if needed. Don't forget to mark as solution andgie kudos if that's works. That's motivate me to keep helping others.

     

    Best regards,

    Oussama (Data Consultant - Expert Fabric/Power BI)

    • SHullen's avatar
      SHullen
      Frequent Visitor
      3 months ago

      Hi Oussama.  Thanks for your assistance with this.  I created it in DAX but still had the same issue.  It's strange.  It's only an issue with "between".  If I change the slicer to dropdown, the values show correctly.  But I need between in this case.  I guess I will try the "What If" solution that was suggested.

       

  • pcoley's avatar
    pcoley
    Icon for Super User rankSuper User
    3 months ago

    SHullen 

    Instead of using the rounded column directly, many people create a What-If Parameter for the slicer (it gives cleaner control for rounded thousands):

    1. Go to Modeling tab → Click New Parameter → What-If Parameter
    2. Configure it like this:
      • Name: Net Profit Slicer (or any name you like)
      • Data type: Whole Number
      • Minimum: 0 (or your lowest reasonable value)
      • Maximum: Enter a high number (e.g. 10000000)
      • Increment: 1000
      • Check Add slicer to this page
    3. Click OK. Power BI will create:
      • A new table called Net Profit Slicer
      • A measure called Net Profit Slicer Value
    4. Create this DAX Measure (very important):
    Net Profit Filtered = 
VAR MinValue = MIN('Net Profit Slicer'[Net Profit Slicer])
VAR MaxValue = MAX('Net Profit Slicer'[Net Profit Slicer])
RETURN
    CALCULATE(
        [Net Profit],
        'YourTable'[NetProfit_Filter] >= MinValue 
        && 'YourTable'[NetProfit_Filter] <= MaxValue
    )
    1. Use the What-If slicer on your page (it will show values like 0, 1000, 2000, etc.).
    2. Replace your existing measures with [Net Profit Filtered] in visuals.

    Advantages:

    • Clean rounded steps (every 1,000)
    • Works much more reliably than a custom column in Between slicer
    • Dynamic and easy to adjust

    ------------

    if you still want to use the column created with powerquery please try:

    • Set the correct Data Type & Format
      • Go to Model view → select the NetProfit_Filter column.
      • Set Data type to Whole Number.
      • In the Formatting section, set Decimal places to 0.
      • (Very important for Between slicers)
    • Refresh everything
      • Close & reopen Power BI Desktop.
      • Refresh the data (or the specific table).
      • Delete the slicer and add it again.
    • Slicer Settings
      • Make sure the slicer is set to Between style.
      • In the slicer settings, try turning "Include blank values" off if it's on.


    ---------------
    Or create the rounding in DAX as a calculated column (after testing the Power Query one).

     

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi @SHullen ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support