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 mon...
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])&"")

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

        Hi Ashish

        I cant seem to get the total of the count rows after i do some filters on the visual. I want the cout of the rows to say 8 instead of 21. The 21 are the count of the SSID  as multiple SSID are in the tabel by year and month so that what it has 21 count of the SSID. How do i get the measure to count 8 vs the 21.

         

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

         

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      In the Query Editor, change the data type of the SSID column to Number.