Total count with filter and without grouping

Question

how can I add a filter to

evaluate( ROW ( "Count" ,COUNTROWS(InternetSales) )
)

let's say, Internet sales has a date column and I want to count all records from date a to date b

Is there a way I could group 2 queries like that using something like ADDCOLUMNS? or maybe a UNION

for example:

select

count(*) InternetSales where date > X and < Y as "first quarter",

count(*) InternetSales where date > W and < Z as "second quarter",

Answer 1

The row function can take multiple name/expression pairs, so the following pattern should work

evaluate ROW (
"first quarter" , CALCULATE( COUNTROWS(InternetSales) , DATESBETWEEN(InternetSales[Date], x,y)),
"second quarter" , CALCULATE( COUNTROWS(InternetSales), DATESBETWEEN(InternetSales[Date], w,z) )
)

---

http://darren.gosbell.com - please mark correct answers

Answer 2

Hi SH_2017,

Thanks for your question.

You can also try below DAX formula:

evaluate 
ROW ( 
 "first quarter" , CALCULATE( COUNTROWS(filter('Internet Sales',
 'Internet Sales'[Order Date]>= Date(2012,01,01) && 
  'Internet Sales'[Order Date]<= Date(2012,03,31)))),
"Second quarter", CALCULATE( COUNTROWS(filter('Internet Sales',
 'Internet Sales'[Order Date]>= Date(2012,04,01) && 
  'Internet Sales'[Order Date]<= Date(2012,06,30))))
 )

---

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]