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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jsummers
Helper I
Helper I

Count if as a Measure

Hi All

 

I have this calculated column in one of my reports: 

Count = COUNTROWS(FILTER('All Current Members','All Current Members'[BusinessID]=EARLIER('All Current Members'[BusinessID])))
 
It works perfectly.  Counts the number of business IDs for each unique ID. However, with the dataset I am currently using, I can not create a calculated column.  I need to replicate as a measure, please can someone help?
Capture.JPG
 
Many thanks 
1 ACCEPTED SOLUTION
v-jianpeng-msft
Community Support
Community Support

Thanks the solution from @MattAllington , and your solution is great.

Hi, @Jsummers 

Pls is your current problem solved? I went through your reply to Matt Allington. If you don't want your measure to be affected by other columns, you can use the ALLEXCEPT function. I created a sample as follows:

vjianpengmsft_0-1713336155743.png

I use the following DAX expression:

Count = CALCULATE(COUNTA('Table'[BusID]),ALLEXCEPT('Table','Table'[BusID]))

This will remove the other filters and leave only the BusID filter. Here are the results:

vjianpengmsft_1-1713336351867.png

When I add the id column to the visual, it doesn't affect the calculation of the Count measure. If you are not familiar with this function, you can click on the link below to learn it:

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

I've provided the PBIX file used this time below.

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

View solution in original post

5 REPLIES 5
v-jianpeng-msft
Community Support
Community Support

Thanks the solution from @MattAllington , and your solution is great.

Hi, @Jsummers 

Pls is your current problem solved? I went through your reply to Matt Allington. If you don't want your measure to be affected by other columns, you can use the ALLEXCEPT function. I created a sample as follows:

vjianpengmsft_0-1713336155743.png

I use the following DAX expression:

Count = CALCULATE(COUNTA('Table'[BusID]),ALLEXCEPT('Table','Table'[BusID]))

This will remove the other filters and leave only the BusID filter. Here are the results:

vjianpengmsft_1-1713336351867.png

When I add the id column to the visual, it doesn't affect the calculation of the Count measure. If you are not familiar with this function, you can click on the link below to learn it:

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

I've provided the PBIX file used this time below.

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

Thank you for your help, this has worked and giving me the expected result. 

You should not use a calculated column to solve this problem. Read about why in my article here

https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Measure Count = COUNTROWS('All Current Members')

put it in a table alone with the BusID. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt, thanks for the quick response.  That works, but as soon as I put other columns in the table, the numbers change, is there anyway to stop the count changing?

By other columns, I assume you mean other columns that are not aggregated. What behaviour are you getting and would you expect?  Can you post an image?  You also need to explain where the columns come from. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.