topic Re: Count function not working? in DAX Commands and Tips

Count function not working?

MikeBresnan — Thu, 10 Jun 2021 20:01:56 GMT

I'm having issues with a count function not working. I tried it as a measure, and as a function within the value, but my count is way off. The field i'm trying to count has all unique values, so as far as i can tell it's not aggregating anything. in my data i have two distinct fields (row id, and doc number) that are both giving the same result. it's weird...

for this matrix visualization, i'm summing the dollar values of incoming funds, and displaying a count of transactions. for one entity, i have one payment with a dollar amount, but the count is showing no value.

Has anyone seen this sort of issue before?

Re: Count function not working?

lbendlin — Sun, 13 Jun 2021 00:02:03 GMT

The question is too vague. Please provide sample data in usable format (not as a picture) and show the expected outcome.

Re: Count function not working?

MikeBresnan — Mon, 14 Jun 2021 13:22:17 GMT

Not sure how to attach a file, but here is a cropped version of the data i'm working with.

<can't add a table?>

I'm creating a very simple matrix visual that totals (for YTD as of March 31, 2021) the dollar amounts (amount_c) and count the number of transactions (either id or name, i've tried both) for the same time frame. I have the CreatedDate field linked to a date table, and use a YTDTOTAL measure based on that date field, and a sum measure to get the dollar amount. that value is correct in the measure, but the count is off.

for CO B, in power bi, i get the correct dollar amount ($1.029 million), but a count of null/0 instead of the correct value: 1. For CO A, i again get the correct dollar amount ($7,569.00) but a count of 2, instead of the correct value: 5

Could there be an issue with the relationship between the date field in the source data and the date table? if i create the same measure on a separate report (same dataset), but base it on the createdate, i get an accurate result. i'm not sure what a potential issue could be? also, i'd assume the dollar amount would incorrect as well, in that case.

Re: Count function not working?

edhans — Mon, 14 Jun 2021 21:57:06 GMT

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Re: Count function not working?

MikeBresnan — Tue, 15 Jun 2021 20:12:08 GMT

OK, lets try this again: here is the link to the cropped version of the sample data

https://drive.google.com/file/d/15z5LWjmO3urWuTaCv5YwKDKY2xdDiEmk/view?usp=sharing

The pivot table in Sheet2 shows how the data should be calculating

and here's how it shows in Power BI

Thanks for all the help/patience!

Re: Count function not working?

Anonymous — Wed, 16 Jun 2021 06:03:49 GMT

Hello MikeBresnan
Is this your expected output?

Re: Count function not working?

MikeBresnan — Wed, 16 Jun 2021 12:59:15 GMT

Yup! that's what i would expect to see. If i create the YTDTOTAL measure based on that created date field, it's what i get. When i use the date table it's linked to, the count gets thrown off.