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 only the parts we have entered in the last 5 days.

 

The table goes back to year 2002.  If, in the last 5 days, I entered a part for the first time since 2002, I want to see it.

 

I tried.....

CALCULATE(
    COUNT(WO_EXPEDITE[PART_NUMBER]),

        ALL(WO_EXPEDITE[ADDED_DTE])) 

....and put a "Relative Date" for "Added_Dte" of the past 5 days on the Page level filter but it doesn't seem to be working correctly.  It is showing me only the parts we entered the past 5 days but it is returning 1 on a part that has been in the shop 6 times.

If my formula is correct, it may be the way I'm linking some other tables to this one.

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

10 Replies

  • 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