DAX for counting values = 'yes' in column

[AMBP1973]

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]

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]

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]

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.

[v-kpoloju-msft]

Hi @AMBP1973,

We haven’t received a response with the requested sample data to help troubleshoot your issue effectively.

To proceed further, please share a minimal and clean sample dataset (not a screenshot) along with the expected output. This helps the community understand and resolve your question
faster.

Thank you.

[v-kpoloju-msft]

Hi @AMBP1973,

Following up again as we still haven't received the required sample data or clarification to assist with your issue. Please provide sample data in text or table format (no screenshots) and Expected output based on that data or Any other clarifying details relevant to the issue.

Thank you.

[sreejad]

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]

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]

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.

[MasonMA]

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 

[Irwan]

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.