Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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.
@joshua1990 Hi!
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?
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |