Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joshua1990
Post Prodigy
Post Prodigy

COUNTROWS with ALL and data selection

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

DateWeekMonth
01.01.202201Jan
02.01.202201Jan

 

 

Transactional Table

DateArticleSales
01.01.2022ABC500
01.01.2022ABCD600
6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

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
Super User
Super User

@joshua1990 Hi!

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

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

@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

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

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

 

BF

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.