Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 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
Icon for Microsoft Employee rankMicrosoft Employee
8 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
    8 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
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous,

       

      Can you share a dummy sample?

       

      Best Regards,

      Dale

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