Pass Filtered Table to Calculate Function

Question

I have a trending table and here is a sample data&nbsp;&nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Key&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13/27/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;My goal is to find the "Key" that has COUNT&gt;1 in the latest date i.e. Key=1 should be outputed since it appeard twice in 3/1/2019&nbsp;My logic is to create a calculated column1- First filter the table to only inlcude latest date records&nbsp;2- Use Earlier function to get the ones that has more than one records in that filtered tableFindDups= SUMX(FILTER(TABLE,TABLE[DATE]=DATE(YEAR(TODAY()),MONTH(TODAY()),27))),IF([KEY]=EARLIER([KEY]),1,0))&nbsp;But the function does not filter the table to only latest date records i.e. 3/27/2019&nbsp;Any clue :) please ?&nbsp;Thank you&nbsp;

alb · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
If there's only one key with count &gt; 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] ) ) &gt; 1 &amp;&amp; Table1[Date] = MAX ( Table1[Date] )
    )
)

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

Forum Discussion

Pass Filtered Table to Calculate Function

1 Reply

Related content

Calculating with Filters

Date filter in Calculate function

Caclulate function over riding filter?

Calculate function - filter

filter function