cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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

 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
1 ACCEPTED SOLUTION
Employee

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] )
)
```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Employee

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] )
)
```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors