cancel
Showing results for
Did you mean:

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

Helper I

## Count if as a Measure

Hi All

I have this calculated column in one of my reports:

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
1 ACCEPTED SOLUTION
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:

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.

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.

5 REPLIES 5
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:

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.

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.

Helper I

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

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.
Helper I

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.