COUNTROWS with ALL and data selection

[joshua1990]

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

Date	Week	Month
01.01.2022	01	Jan
02.01.2022	01	Jan

Transactional Table

Date	Article	Sales
01.01.2022	ABC	500
01.01.2022	ABCD	600

[v-angzheng-msft]

Hi, @joshua1990 

Try this:
1)

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

2)

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.

[BeaBF]

@joshua1990 Hi!

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

BF

[joshua1990]

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

[BeaBF]

@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

BF

[joshua1990]

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

[BeaBF]

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

BF