Count if as a Measure

[Jsummers]

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?

Many thanks 

[v-jianpeng-msft · Accepted solution]

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:

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:

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.

[Jsummers]

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

[MattAllington]

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. 

[Jsummers]

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?

[MattAllington]

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.