Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate & Filter issues

I am having a brain freeze - i want the data table(1) below to aggregate  to table (2).   Days over 30 = DateDiff(Date Received, MaxDate,day)   Recordcount = CountRows(tablename)

 

What filter or calculation will give me the aggregation that i need? 

 

I am working this: Customer Alert = COUNTROWS(FILTER(tablename,CALCULATE(DISTINCTCOUNT(tablename[Days Over 30]))<=30,[Recordcount] <= 3))

 

But it does not except two filters, so i am breaking it into 2 measures. 

Any help would be appreciated.

 

THanks

 

 

Table 1

SONameDays Over 30RecordcountDate Received
30911AMERICAN714/10/2017
30653Dakota514/12/2017
30932METALS514/12/2017
30933METALS514/12/2017
30934METALS514/12/2017
30935METALS514/12/2017
30930Innovative614/11/2017
30757IOWA614/11/2017
30758IOWA614/11/2017
30937FLETCH614/11/2017
30938FLETCH614/11/2017
30939FLETCH614/11/2017
30940FLETCH614/11/2017
30941FLETCH614/11/2017
30942FLETCH614/11/2017
30780INDIANAPOLIS514/12/2017
30894ROANOKE614/11/2017
30918NORTH AMERICA514/12/2017

 

Table 2

NameRecord count
Metals4
Fletch6
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    In your scenario, please first modify the DAX formula for Recordcount as below.

    Create a calculated column [Recordcount] in Table1

    Recordcount =
CALCULATE ( COUNT ( Table1[Name] ), ALLEXCEPT ( Table1, Table1[Name] ) )

     

    Then, in order to get the Table2 output, you should create a calculated table using this formula:

    Table2 =
SUMMARIZE (
    FILTER ( Table1, Table1[Days over 30] <= 30 && Table1[Recordcount] > 3 ),
    Table1[Name],
    "Record Count", MAX ( Table1[Recordcount] )
)

     

    If you still have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

2 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    9 years ago

    With FILTER you have to use bitwise notation (&&, ||).  CALCULATE can do the implicit AND in multiple successive filters:

     

    Customer Alert =
COUNTROWS (
    FILTER (
        tablename,
        CALCULATE ( DISTINCTCOUNT ( tablename[Days Over 30] ) ) <= 30
            && [Recordcount] <= 3    )
)

     

    Hope this helps

    David

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    In your scenario, please first modify the DAX formula for Recordcount as below.

    Create a calculated column [Recordcount] in Table1

    Recordcount =
CALCULATE ( COUNT ( Table1[Name] ), ALLEXCEPT ( Table1, Table1[Name] ) )

     

    Then, in order to get the Table2 output, you should create a calculated table using this formula:

    Table2 =
SUMMARIZE (
    FILTER ( Table1, Table1[Days over 30] <= 30 && Table1[Recordcount] > 3 ),
    Table1[Name],
    "Record Count", MAX ( Table1[Recordcount] )
)

     

    If you still have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu