Calculate & Filter issues

[Anonymous]

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

SO	Name	Days Over 30	Recordcount	Date Received
30911	AMERICAN	7	1	4/10/2017
30653	Dakota	5	1	4/12/2017
30932	METALS	5	1	4/12/2017
30933	METALS	5	1	4/12/2017
30934	METALS	5	1	4/12/2017
30935	METALS	5	1	4/12/2017
30930	Innovative	6	1	4/11/2017
30757	IOWA	6	1	4/11/2017
30758	IOWA	6	1	4/11/2017
30937	FLETCH	6	1	4/11/2017
30938	FLETCH	6	1	4/11/2017
30939	FLETCH	6	1	4/11/2017
30940	FLETCH	6	1	4/11/2017
30941	FLETCH	6	1	4/11/2017
30942	FLETCH	6	1	4/11/2017
30780	INDIANAPOLIS	5	1	4/12/2017
30894	ROANOKE	6	1	4/11/2017
30918	NORTH AMERICA	5	1	4/12/2017

Table 2

Name	Record count
Metals	4
Fletch	6

[v-yulgu-msft · Accepted solution]

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]

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