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

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