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

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?

     

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      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

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