Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

COUNT ROWS BY MONTH and YEAR base off a filter

Hi All

I am tying to calulate the total number of rows by year and month as seen in the firs tabel beow but want to count the SSID rows where the SSIDSUPP_DISRUPTION is over 1200 points for that month. In the example below I want the 2020 Jan rw to say 2. Since there are only 2 SSID over the 1200 point. Below is the Mesure i tried but give me 85 as in thetable below.

 

Measure = CALCULATE(COUNTROWS('SQ Audit Test Data'),FILTER('SQ Audit Test Data','SQ Audit Test Data'[SSID]<=MAX('SQ Audit Test Data'[SSID])))
 

 

 

 

Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Does this work

    Surveyor Measure = IF(ISINSCOPE('SQ Audit Test Data'[SSID]),SELECTEDVALUE('SQ Audit Test Data'[SSID]),distinctcount('SQ Audit Test Data'[SSID])&"")

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Does this measure work?

    Measure = CALCULATE(COUNTROWS('SQ Audit Test Data'),'SQ Audit Test Data'[SSID]>=1200)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Ashish

      When using the measure it say "Can't Display thetVisual". The Format = Whole Number

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I found the issue, I was using SSID which is a text field and change it to SUPP_DISRUPTION ,which is a number. When I did this the error went away but would not update the table proplerly when I aadded the measure field.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks this does work.