locked
Counting the number of time the minimum value is reached (DAX version Excel 2010) RRS feed

  • Question

  • I need to calculate the number of time the min value is reach but I just can't figure out how to get it

    This is a quick snapshot of the data

    and this is the expected results

    The min column is calculated as =MIN('Production'[Value])

    and the min count is calculated as

    =CALCULATE(COUNTROWS(Production), Production[Value] = 12)   and got the right result of 5

    But I don't want to hard code the min value as the result is different for each product.

    I have tried

    =COUNTROWS(filter(Production, [Value] = [MinVal]))  where MinVal is a new calculated measure with the min value

    and

    =CALCULATE(COUNTROWS(Production), filter(VALUES(Production[Value]), Production[Value] =  Production[MinVal]))

    Both time I got a result of 8

    I don't change formulas often, so it's almost back to basic everytime


    DanielP38

    Thursday, March 23, 2017 8:20 PM

Answers

  • If that's how you will set up you pivot, with Product ID in rows, then removing the ALL() function should work for you:

    =COUNTROWS(
              FILTER(
                   Production,
                   Production[Value] = MIN(Production[Value])
               )
           )

    Tuesday, March 28, 2017 1:56 PM
    Answerer

All replies

  • Try something like this:

    =COUNTROWS(
              FILTER(
                   ALL(Production),
                   Production[Value] = MIN(Production[Value])
               )
           )
    This should give the count of the Min Value across the entire table as a standalone measure without any filters applied.  If you need something that filters differently based on another field, please explain more and provide a working snapshot of the data/expected results.

    Thursday, March 23, 2017 10:35 PM
    Answerer
  • Hi Danielp38,

    Please try the formula as @Mike posted, please post your sample data and list expected result if it doesn't resolve your issue.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 3:19 AM
  • Thanks but I forgot to mention one important requirement

    I need to calculate the MIN by product not the MIN of all the values

    Product Count Min Min Count Answer
    2485 8 12 5 11
    6149 9 12 6 11
    15226 19 20 19 22
    15431 2 4 2 2
    Total  38 4 11 2

    The column Min Count show what I looking for and "Answer" is the result of the proposed answer.

    A partial list of the raw data looks like that

    RowID Date Value ProductID
    114965978 2017-03-15 20 2485
    114959679 2017-03-02 20 2485
    114959675 2017-02-28 20 2485
    114959664 2017-02-23 12 2485
    114959657 2017-02-21 12 2485
    114959646 2017-02-16 12 2485
    114959636 2017-02-14 12 2485
    114959621 2017-02-07 12 2485
    114904887 2017-03-16 4 15431
    114904795 2017-03-13 4 15431
    114966037 2017-03-17 20 15226
    114965933 2017-03-10 20 15226
    114965356 2017-02-14 20 15226
    114965290 2017-02-10 20 15226
    114965822 2017-03-07 20 15226
    114965759 2017-03-03 20 15226
    114965674 2017-02-28 20 15226
    114965616 2017-02-24 20 15226
    114965194 2017-02-07 20 15226
    114965138 2017-02-03 20 15226
    114965061 2017-01-31 20 15226
    114965020 2017-01-27 20 15226
    114965495 2017-02-21 20 15226
    114965451 2017-02-17 20 15226
    114964934 2017-01-24 20 15226
    114964885 2017-01-20 20 15226
    114964814 2017-01-17 20 15226
    114964775 2017-01-13 20 15226
    114964642 2017-01-06 20 15226
    114965730 2017-03-02 12 6149
    114965670 2017-02-28 12 6149
    114965219 2017-02-08 12 6149
    114965572 2017-02-23 12 6149
    114965494 2017-02-21 12 6149
    114965432 2017-02-17 12 6149
    114965953 2017-03-13 18 6149
    114965890 2017-03-09 18 6149
    114965823 2017-03-07 18 6149

    Thanks again for your precious help


    DanielP38

    Tuesday, March 28, 2017 1:38 PM
  • If that's how you will set up you pivot, with Product ID in rows, then removing the ALL() function should work for you:

    =COUNTROWS(
              FILTER(
                   Production,
                   Production[Value] = MIN(Production[Value])
               )
           )

    Tuesday, March 28, 2017 1:56 PM
    Answerer
  • Looking good,

    I looks like what I wanted.

    Thanks a lot


    DanielP38

    Tuesday, March 28, 2017 2:54 PM
  • Hi Mike,

    Why are you unmark the answer, there is somthing wrong?

    Best Regards,
    Angelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 31, 2017 1:59 AM
  • The wrong comment was marked as the answer.
    Friday, March 31, 2017 3:09 AM
    Answerer
  • Hi Danielp38,

    You should mark the helpful or right reply as answer, thanks for understanding.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 31, 2017 3:33 AM