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

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

Hi Anonymous,

 

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

 

Best Regards,

Dale