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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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])))
 

bu965_2-1619476851869.png

 

bu965_1-1619476589361.png

 

 

1 ACCEPTED SOLUTION

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Thanks this does work.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Measure = CALCULATE(COUNTROWS('SQ Audit Test Data'),'SQ Audit Test Data'[SSID]>=1200)

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish

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

 

bu965_0-1619527341818.png

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

bu965_1-1619528110067.pngbu965_2-1619528220078.png

 

 

Anonymous
Not applicable

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')&"")
 
bu965_0-1619610463573.png

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors