Forum Discussion

Jsummers's avatar
Jsummers
Helper I
2 years ago
Solved

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?
 
Many thanks 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

5 Replies

    • Jsummers's avatar
      Jsummers
      Helper I
      2 years ago

      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's avatar
        MattAllington
        Community Champion
        2 years ago

        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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Jsummers
      Helper I
      2 years ago

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