Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
4 years ago

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
Need Help

6 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    4 years ago

    joshua1990 Hi!

    CALCULATE(COUNTROWS(Dates[Week]), [Sales] < 500, ALLSELECTED(Dates[Week]))
     
    BF
    • joshua1990's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

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

      • BeaBF's avatar
        BeaBF
        Icon for Super User rankSuper User
        4 years ago

        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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.