Count Data based on Filter

[mbryant2014]

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
1	6	3/5/2020
1	7	3/5/2020
1	8	3/5/2020
1	9	3/6/2020
1	10	3/7/2020
1	11	3/8/2020
1	12	3/9/2020
1	13	3/10/2020

Assume I have this table also linked to a dimCalendar table where the Sale Date is linked to a Date field.  I have a slicer using the dimCalendar Date table.  I want to be able to count the number of Sales dates based on the Date filter visual that I'm using.  How can I make this happen?

Thanks!

[mbryant2014]

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.

SiteName	Customerid	readingDate	ReadingDateTime	ReadingID	Second Sale
AA	149673	3/6/2020	3/6/2020 17:13	11734772
AA	193545	3/6/2020	3/6/2020 15:35	11734502
AA	193545	3/6/2020	3/6/2020 16:06	11734544
AA	193545	3/7/2020	3/7/2020 14:35	11737585
AA	193545	3/7/2020	3/7/2020 14:56	11737625
AA	193545	3/7/2020	3/7/2020 15:07	11737663
AA	193545	3/8/2020	3/8/2020 6:52	11739291
AA	193545	3/9/2020	3/9/2020 15:56	11744023
AA	193545	3/9/2020	3/9/2020 16:16	11744066
AA	232689	3/4/2020	3/4/2020 15:13	11727001
AA	232689	3/9/2020	3/9/2020 16:29	11744084
AA	390329	3/6/2020	3/6/2020 17:38	11734881
AA	407995	3/1/2020	3/1/2020 15:27	11717027
AA	453425	3/6/2020	3/6/2020 15:01	11734365
AA	540117	3/7/2020	3/7/2020 17:05	11737977
AA	546209	3/7/2020	3/7/2020 17:24	11738028
AA	546209	3/7/2020	3/7/2020 17:35	11738063
AA	563988	3/4/2020	3/4/2020 16:18	11727197
AA	574994	3/7/2020	3/7/2020 3:53	11736090
AA	574994	3/7/2020	3/7/2020 4:06	11736104
AA	602318	3/7/2020	3/7/2020 7:29	11736408
AA	620595	3/9/2020	3/9/2020 16:52	11744188
AA	627202	3/4/2020	3/4/2020 15:00	11726909
AA	671295	3/7/2020	3/7/2020 7:20	11736377
AA	695742	3/8/2020	3/8/2020 4:21	11739142
AA	796993	3/7/2020	3/7/2020 6:34	11736292
AA	808290	3/6/2020	3/6/2020 16:25	11734614
AA	874697	3/7/2020	3/7/2020 15:52	11737784	6/14/2018
AA	879467	3/8/2020	3/8/2020 6:21	11739235	7/12/2018
AA	908130	3/8/2020	3/8/2020 4:44	11739162	11/30/2018
AA	939666	3/7/2020	3/7/2020 6:08	11736265	3/23/2019
AA	955062	3/7/2020	3/7/2020 15:20	11737698	6/16/2019
AA	993219	3/8/2020	3/8/2020 7:28	11739344	7/31/2018
AA	1019215	3/7/2020	3/7/2020 5:31	11736192	2/20/2020
AA	1020411	3/7/2020	3/7/2020 3:34	11736072	2/18/2020
AA	1021345	3/7/2020	3/7/2020 4:30	11736132	2/20/2020
AA	1021681	3/7/2020	3/7/2020 16:36	11737910	3/6/2020
AA	1026583	3/8/2020	3/8/2020 6:01	11739203	3/9/2020
AA	1026591	3/8/2020	3/8/2020 7:12	11739326	3/9/2020
AA	1027154	3/9/2020	3/9/2020 16:40	11744140	3/10/2020

[v-kelly-msft]

Hi @mbryant2014 ，

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!

[mbryant2014]

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!

[v-kelly-msft]

Hi @mbryant2014 ，

Drag  column Sales of  the table to a card visual, right click the field and choose" count":

Or you can create a measure as below:

measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('table 2','table 2'[Date]))

Then put the calendar date to a slicer :

Finally you can fulfill what you need.

Here is the related .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@mbryant2014 

If you date is connected to Date calendar you put that on any visual any of the fields from the sales table and change aggregation on the fly by right click on the field in the visualization pane

Or create like

Cnt = count(table[Cust Id])

Dis Cnt = distinctcount(table[Cust Id])

Cnt = count(table[AgentId])

Dis Cnt = distinctcount(table[Agent Id])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[HotChilli]

Pull the Sale on to a card. Change the aggregation in the Values well to Count.

OR

Create a measure like CountSale = COUNT(TableX[Sale])  and drag that on to a card