topic Re: COUNTROWS with ALL and data selection in DAX Commands and Tips

COUNTROWS with ALL and data selection

joshua1990 — Tue, 08 Mar 2022 10:43:11 GMT

Hi experts!

I use a simple COUNTROWS Measur to count the number of weeks with a value below a target:

COUNTROWS(FILTER(ALL(Dates[Week]), [Sales] < 500))

I use a slicer in the same visual to display just a specific month or quarter but since there is ALL in the function, this is not working.

How can I get the number of weeks always for the selected date range?

Calendar Table

Transactional Table

BeaBF — Tue, 08 Mar 2022 10:24:10 GMT

CALCULATE(COUNTROWS(Dates[Week]), [Sales] < 500, ALLSELECTED(Dates[Week]))

joshua1990 — Tue, 08 Mar 2022 10:25:36 GMT

Thanks, but this measure will not work. COUNTROWS allows just tables, not columns

BeaBF — Tue, 08 Mar 2022 10:28:08 GMT

joshua1990 so use COUNT, why do you need to use COUNTROWS? Can't you count the rows of the entire table?

It would be easier if you paste some sample data and show the result you would like to get

joshua1990 — Tue, 08 Mar 2022 10:44:14 GMT

BeaBF : please find in the initial post the structure of the tables. Works COUNT if you have multiple rows with the same week?

BeaBF — Tue, 08 Mar 2022 11:08:20 GMT

joshua1990 Can you show me the expected result based on the sample data you have write above?

v-angzheng-msft — Mon, 14 Mar 2022 07:14:30 GMT

Hi, joshua1990

Try this:
1)

measure = COUNTROWS ( FILTER ( ALLSELECTED ( Dates[Week] ), [Sales] < 500 ) )

measure = COUNTROWS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar Table', 'Transactional Table'[Sales] < 500 ), [Week] ) )

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.