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 c...
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

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