Forum Discussion

bernate's avatar
bernate
Icon for Helper III rankHelper III
2 years ago
Solved

Exclude 0 from MINX Result

Hello all, 

 

I'm trying to get the lowest sum sales value of all days in a month. So in the example below I want to display 5,759 as the value in a card, not 0.

 

Here is the DAX formula I'm using. It returns the correct result since 0 is the min value, but is there some way I can exclude 0 from the calculation so it jumps to the next minimum value (5,759)?

 

=MINX(SUMMARIZE('Sales Data','DateTable'[CalendarDt],"Total Sales",SUM('Sales Data'[Daily Sales])),[Total Sales])
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    = MINX( FILTER(
   SUMMARIZE('Sales Data','DateTable'[CalendarDt],"Total Sales",SUM('Sales Data'[Daily Sales])),[Total Sales]>0)
,[Total Sales])

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

    = MINX( FILTER(
   SUMMARIZE('Sales Data','DateTable'[CalendarDt],"Total Sales",SUM('Sales Data'[Daily Sales])),[Total Sales]>0)
,[Total Sales])
    • bernate's avatar
      bernate
      Icon for Helper III rankHelper III
      2 years ago

      Thank you so much, I knew I was overlooking something and I completely forgot about the filter function. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Does this measure work?

    Measure = MINX('DateTable',[Total Sales])

    If it does not work, then share the download link of the PBI file.