Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count Data based on Filter

Hello - I'm sure this is simple but I'm stumped.  Below is some sample data:   Agent ID Cust ID Sale 1 1 3/1/2020 1 2 3/2/2020 1 3 3/3/2020 1 4 3/3/2020 1 5 3/4/2020 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for the replies.  I don't think I gave a good explanation of the issue.  I'll try and give a better explanation with some expanded data.  I have 2 tables, a readings table and a date table.  The date table is simply a list of dates.  The readings table has all of the pertinent date.  I have the 2 tables joined via the readingDate field.  I have a slicer visual that is using the Date field in the date table.  What I want to do is to count all of the Second Sale rows that fall within the slicer selection.  So based on the below data, if the slicer is set to 3/5/20-3/9/30 - I'd expect to see 3 retuned on a card visual.  Is this possible?  I tried the solutions presented and they don't seeme to working as I imagined.  Thanks!

 

One side note - the second sale field is actually a related measure that is coming from a customer table joined to the readings table via the customerid.

 

SiteNameCustomeridreadingDateReadingDateTimeReadingIDSecond Sale
AA1496733/6/20203/6/2020 17:1311734772 
AA1935453/6/20203/6/2020 15:3511734502 
AA1935453/6/20203/6/2020 16:0611734544 
AA1935453/7/20203/7/2020 14:3511737585 
AA1935453/7/20203/7/2020 14:5611737625 
AA1935453/7/20203/7/2020 15:0711737663 
AA1935453/8/20203/8/2020 6:5211739291 
AA1935453/9/20203/9/2020 15:5611744023 
AA1935453/9/20203/9/2020 16:1611744066 
AA2326893/4/20203/4/2020 15:1311727001 
AA2326893/9/20203/9/2020 16:2911744084 
AA3903293/6/20203/6/2020 17:3811734881 
AA4079953/1/20203/1/2020 15:2711717027 
AA4534253/6/20203/6/2020 15:0111734365 
AA5401173/7/20203/7/2020 17:0511737977 
AA5462093/7/20203/7/2020 17:2411738028 
AA5462093/7/20203/7/2020 17:3511738063 
AA5639883/4/20203/4/2020 16:1811727197 
AA5749943/7/20203/7/2020 3:5311736090 
AA5749943/7/20203/7/2020 4:0611736104 
AA6023183/7/20203/7/2020 7:2911736408 
AA6205953/9/20203/9/2020 16:5211744188 
AA6272023/4/20203/4/2020 15:0011726909 
AA6712953/7/20203/7/2020 7:2011736377 
AA6957423/8/20203/8/2020 4:2111739142 
AA7969933/7/20203/7/2020 6:3411736292 
AA8082903/6/20203/6/2020 16:2511734614 
AA8746973/7/20203/7/2020 15:52117377846/14/2018
AA8794673/8/20203/8/2020 6:21117392357/12/2018
AA9081303/8/20203/8/2020 4:441173916211/30/2018
AA9396663/7/20203/7/2020 6:08117362653/23/2019
AA9550623/7/20203/7/2020 15:20117376986/16/2019
AA9932193/8/20203/8/2020 7:28117393447/31/2018
AA10192153/7/20203/7/2020 5:31117361922/20/2020
AA10204113/7/20203/7/2020 3:34117360722/18/2020
AA10213453/7/20203/7/2020 4:30117361322/20/2020
AA10216813/7/20203/7/2020 16:36117379103/6/2020
AA10265833/8/20203/8/2020 6:01117392033/9/2020
AA10265913/8/20203/8/2020 7:12117393263/9/2020
AA10271543/9/20203/9/2020 16:40117441403/10/2020

 

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ，

 

You need a measure as below：

 

 

Measure = 
IF(SELECTEDVALUE('Table'[Second Sale]) in VALUES('Table 2'[Date]),SELECTEDVALUE('Table'[Second Sale]),BLANK())

 

 

Finally you will see:

 

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Kelly - thanks!

     

    One more question - how can I get this into a Card visual with a count of the returned values.  In your example, I'd like to use a card visual displaying 3.  Make sense?

     

    thanks!