Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count with All filter

I have a single table which contains the following:   Part     Added_Date     The question is:  1.  I want to see when we are entering a part for the first time, but I need it narrowed down to ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Try this one please.

    Measure =
VAR existedBefore5 =
    CALCULATETABLE (
        VALUES ( Table1[Part] ),
        FILTER ( ALL ( Table1 ), Table1[Added_Date] <= TODAY () - 5 )
    )
VAR amount =
    CALCULATE (
        COUNT ( Table1[Part] ),
        FILTER ( 'Table1', NOT Table1[Part] IN existedBefore5 )
    )
RETURN
    IF ( ISBLANK ( amount ), BLANK (), IF ( amount <= 5, amount, 9999 ) )
//9999 could be 0 or blank()

     

    If it works, please mark it as an answer.

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for the help.  I might of explained this incorrectly.  

 

First, I want to see whenever we enter a part for the first time.  This is the most important.

 

Second, I don't want to see a part that was entered for the first time a year ago.  I want to narrow down what is being displayed to just recent activity.  If we enter a part for the first time within the last 5 days of business, I have time to react and monitor it through the shop.  

 

The only importance to the "last 5 days" is that it is recent activity and that's what I want to display.

 

Is that what your measure will display?  I'm trying to figure it out.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

Can you share a dummy sample?

 

Best Regards,

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Yes I can. I can supply it tomorrow morning when I get back in the office. Thank you for your help.
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Sample Data

     

    This is some sample data

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous,

       

      I checked my formula again. I think it can work out your requirement. You can filter out the blanks. Did you try it out? 

      Let's verify your scenario again with an example, the first five days are the five days you need. So the result should be "b" and "c" due to a has appeared before 5 days. Right?

       

      2018-09-12   a

      2018-09-11   b

      2018-09-10   c

      2018-09-9    a

      2018-09-8   a

      2018-08-1   a

       

      Best Regards,

      Dale

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        It looks like this is working perfectly.  However, my production manager, whom I am building this information for, just asked if he could see not only the parts that are entered for the first time, but he would like to see the parts if they have been entered 5 or fewer times.

         

        I tried to add a filter of Count(Part) <= 5 to the variable you created but I kept getting an error message.  Could you please show me how to modify the formula you provided to show <= 5, instead of just the first time, like your solution currently shows?