cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Pass Filtered Table to Calculate Function

I have a trending table and here is a sample data

Date                         Key

1/27/2019                     1

1/27/2019                     2

2/27/2019                     1

2/27/2019                     2

3/27/2019                     1

3/27/2019                     1

My goal is to find the "Key" that has COUNT>1 in the latest date i.e. Key=1 should be outputed since it appeard twice in 3/1/2019

My logic is to create a calculated column

1- First filter the table to only inlcude latest date records

2- Use Earlier function to get the ones that has more than one records in that filtered table

FindDups= SUMX(FILTER(TABLE,TABLE[DATE]=DATE(YEAR(TODAY()),MONTH(TODAY()),27))),IF([KEY]=EARLIER([KEY]),1,0))

But the function does not filter the table to only latest date records i.e. 3/27/2019

Thank you

1 ACCEPTED SOLUTION
Super User III

Hi @Anonymous

If there's only one key with count > 1 for the last date, you can create a measure and place it in a card visual:

```Measure =
CALCULATE (
DISTINCT ( Table1[Key] ),
FILTER (
Table1,
CALCULATE ( COUNT ( Table1[Key] ) ) > 1 && Table1[Date] = MAX ( Table1[Date] )
)
)
```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Super User III

Hi @Anonymous

If there's only one key with count > 1 for the last date, you can create a measure and place it in a card visual:

```Measure =
CALCULATE (
DISTINCT ( Table1[Key] ),
FILTER (
Table1,
CALCULATE ( COUNT ( Table1[Key] ) ) > 1 && Table1[Date] = MAX ( Table1[Date] )
)
)
```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Announcements