Count with All filter

[Anonymous]

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.

[v-jiascu-msft]

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 )
    )

Best Regards,

Dale

[Anonymous]

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]

Hi @Anonymous,

Can you share a dummy sample?

Best Regards,

Dale

[Anonymous]

Sample Data

This is some sample data

[v-jiascu-msft]

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]

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?

[v-jiascu-msft · Accepted solution]

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]

It does not seem to be working.  I changed the "Amount <= 5, Amount, 99999" to "Amount <=500, Amount, 99999" and the results did not change.  Did I do something wrong?

Count Times in Shop = 
VAR existedBefore5 = CALCULATETABLE(VALUES(WO_EXPEDITE[PART_ENDED_AS] ),FILTER(ALL(WO_EXPEDITE), WO_EXPEDITE[ADDED_DTE] <= TODAY() - 5)) 

VAR Amount = CALCULATE(COUNT(WO_EXPEDITE[PART_ENDED_AS]), FILTER(WO_EXPEDITE, NOT WO_EXPEDITE[PART_ENDED_AS] IN existedBefore5)) 

RETURN 

CALCULATE( IF( ISBLANK( Amount), BLANK(), IF( Amount <= 5, Amount, 99999))) //COUNT(WO_EXPEDITE[PART_ENDED_AS]), FILTER(WO_EXPEDITE, NOT WO_EXPEDITE[PART_ENDED_AS] in existedBeofre5))

[v-jiascu-msft]

Hi @Anonymous,

Did work in your sample file? Then we can troubleshoot it in your real data.

Best Regards,

Dale

[Anonymous]

Yes I can. I can supply it tomorrow morning when I get back in the office. Thank you for your help.