cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LoveData Regular Visitor
Regular Visitor

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
 
Any clue 🙂 please ?
 
Thank you


 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Pass Filtered Table to Calculate Function

Hi @LoveData 

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  Datanaut

View solution in original post

1 REPLY 1
Super User
Super User

Re: Pass Filtered Table to Calculate Function

Hi @LoveData 

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  Datanaut

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 249 members 2,450 guests
Please welcome our newest community members: