Forum Discussion
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 |
| 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!
6 Replies
- HotChilliCommunity Champion
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
- amitchandakSuper User
Anonymous
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/ - v-kelly-msftCommunity Support
Hi Anonymous ,
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,
KellyDid I answer your question? Mark my post as a solution! - AnonymousNot applicable
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-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!- AnonymousNot applicable
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!