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

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

Hi Anonymous,

 

We need to find out these part that exists 5 days ago first. Please try this formula.

Measure =
VAR existedBefore5 =
    CALCULATETABLE (
        VALUES ( Table1[Part] ),
        FILTER ( ALL ( Table1 ), Table1[Added_Date] <= TODAY () - 5 )
    )
RETURN
    CALCULATE (
        COUNT ( Table1[Part] ),
        FILTER ( 'Table1', NOT Table1[Part] IN existedBefore5 )
    )

Count_with_All_filter

 

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