Forum Discussion

AMBP1973's avatar
AMBP1973
Helper III
1 year ago
Solved

DAX for counting values = 'yes' in column

Hello, seeking assistance in resolving an issue with the result I am getting from the follwing DAX:

 

CALCULATE(COUNTROWS('Assurance Activities'),FILTER('Assurance Activities','Assurance Activities'[ERP Impact]="Yes"))
 
Essentially, I am trying to count the 'yes' values in a column, however I am receiving a result of over 8000+, which should be around the 20 mark, could someone please assist in identifying if the DAX is incorrect?
 
Thankyou in advance. 
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi AMBP1973,

    Thanks for clarifying, and I completely understand you cannot share the real dataset due to confidentiality.

    To help us troubleshoot without exposing sensitive data, could you try creating a small mock dataset that follows the same column names, data types, and general patterns as your real data, but with all values replaced or randomized? Even 5–10 sample rows are often enough for us to test.

    Here is a guide you can use to create and share such data safely:
    Get samples for Power BI - Power BI | Microsoft Learn

    If creating a mock dataset isn’t possible, another option is to share:

    • The structure/schema of your tables (column names + data types only)
    • The exact DAX formulas or Power Query steps you’ve tried
    • A screenshot of the error messages you’re encountering

    With that information, we can try to reproduce the issue in our own test environment and work toward a solution.

    Thank you.

11 Replies

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    Hi AMBP1973, 

    Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to sreejad, MasonMA, Irwan, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

    I am also including .pbix file for your better understanding, please have a look into it:

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

    • AMBP1973's avatar
      AMBP1973
      Helper III
      1 year ago

      Hello v-kpoloju-msft @sreejad, @MasonMA, @Irwan, 

       

      Thankyou for your responses. Unfortunately I am still having issues. I have tried all the solutions but either get errors or the same 8k+ count, or just a count of 1. I have responded directly to sreejad directly as I found that these may be closer to the result I may need. Unfortuantely I cannot share the specific data due to organisational confidentiality.

       

      appreciate any additional advice 🙏

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        Hi AMBP1973,

        Thanks for clarifying, and I completely understand you cannot share the real dataset due to confidentiality.

        To help us troubleshoot without exposing sensitive data, could you try creating a small mock dataset that follows the same column names, data types, and general patterns as your real data, but with all values replaced or randomized? Even 5–10 sample rows are often enough for us to test.

        Here is a guide you can use to create and share such data safely:
        Get samples for Power BI - Power BI | Microsoft Learn

        If creating a mock dataset isn’t possible, another option is to share:

        • The structure/schema of your tables (column names + data types only)
        • The exact DAX formulas or Power Query steps you’ve tried
        • A screenshot of the error messages you’re encountering

        With that information, we can try to reproduce the issue in our own test environment and work toward a solution.

        Thank you.

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello AMBP1973 

     

    i copy your DAX and it works well.

     

    your DAX is counting row in table with yes value.

    is there any filter to get 20marks? like datetime or id filter?

     

    Thank you.

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    Hello AMBP1973 

     

    If it is a Measure it should be working as expected in report.

     

    As mentioned in your post, If it is a calculated column in a table I would also create a flag column like below:  

     

    Flag =
    IF(
    'Assurance Activities'[ERP Impact] = "Yes",
    1,
    0
    )

    and sum the values in reporting. 

    If not working please share some sample data for measure debugging. 

    Thanks 

     

  • sreejad's avatar
    sreejad
    Frequent Visitor
    1 year ago

    Hello AMBP1973 

     

    I copied your dax , but if it has duplicate data those rows will also be counted, if there is any particular column you can consider you can use below dax

    Measure2 = CALCULATE(DISTINCTCOUNT(Sheet1[Column1]),FILTER(Sheet1,Sheet1[Column2]="Yes"))

    or else if you want to count distinct rows in entire table then use below

    Measure = CALCULATE(COUNTROWS(DISTINCT(FILTER(Sheet1,Sheet1[Column2]="Yes"))))

     

    thanks.

    • AMBP1973's avatar
      AMBP1973
      Helper III
      1 year ago

      Hello sreejad , thankyou for your solution. Yes, it seems as though it is counting duplicate rows instead of doing a distinc count of the values.

      I have tried your 'measure2' sugggested DAX but am only receiving a count of 1 as a result? Is there anything I have done incorrectly based on the below?

      CALCULATE(DISTINCTCOUNT('Assurance Activities'[ERP Impact]),FILTER('Assurance Activities','Assurance Activities'[ERP Impact]="Yes"))
       
      Your additional measure is still resulting in a count of 8712, could advise on why the may be happening?
      CALCULATE(COUNTROWS(DISTINCT(FILTER('Assurance Activities','Assurance Activities'[ERP Impact]="Yes"))))
       
      Unfortunately I am unable to share the data due to organsiational privacy issues.
       
      Thanks in advance
      • sreejad's avatar
        sreejad
        Frequent Visitor
        1 year ago

        Hello AMBP1973 

         

        you are using same column for filtering and count as well that is the reason you got 1. we have to consider column for which we need to find count for example you mentioned like you have to get 20 count based on which column your determining that use that column. In formula i mentioned i have used different column

        Hope it works.

         

        Thanks.