Counting Rows with Zero Values

[PanosIWA]

Plesae Help

We are trying to update a system with proper contact information.  The data I'm trying to report off of (attached) shows which accounts are missing an email, address and phone number (shown by zero values). 

I am trying to track and show how many updates each user has to perform.  Any data value greater than zero doesn't need an update.  For example:  Going by data in the screen shot attached.... Marianne has to update 4 emails, 3 addresses, & 4 phone numbers.  And Marta has to update 2 emails, 1 address, and 2 phone numbers.  

I want to build a bar graph (by Admin) showing a count of emails, addresses, and Phone numbers that they each need to update.  Ideally would be good for them to be able to right click the visual and show data, so they can see which accounts they need to update.  

[Anonymous]

There are a few ways to achieve this, depending on what your desired intent is.  This will also depend on when your counts are calcuated.

If they are calculated either before or during your import.  You could create another field which takes the minimum value of those 3 fields.  This would give you a single field to check against and only show where that field is 0.  This could be used in your Report/Page/Visual level filters, or called by measures.

[PanosIWA]

I need each admin to have a seperate count (number of zeros per account) for email, address, and phone.  As they update the system I will import the latest numbers.  Not sure how I can go about this.  I would prefer to have calculated columns that show that info.   Something like the attached graph but obviously this one is calculating wrong. 

[Anonymous]

Ok thats very easy then.  In this case we won't need calculated columns because you have all the data you need.  We'll simply create a set of measures to do the job for you.

Create an initial measure that will do the basic count.  This would be something like:

Count Final Data = COUNTROWS('Final Data Set')

Next we create 3 measures to count each type seperately.

Count PBI Email = CALCULATE(
	[Count Final Data],
	'Final Data Set'[PBI Email] = 0
)

Count PBI Total Address = CALCULATE(
	[Count Final Data],
	'Final Data Set'[PBI Total Address] = 0
)

Count PBI Total Phone = CALCULATE(
	[Count Final Data],
	'Final Data Set'[PBI Total Phone] = 0
)

Put those into your graph and it should work.

[JayneDoe]

Hello there,

Unfortunately this method didn't work for me and I received syntax:

Couldn't load the data for this visual

MdxScript(Model) (13, 2) Calculation error in measure 'Sales Data'[Count SKUs]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

Please try again later or contact support. If you contact support, please provide these details.

Any help would be great!

[PanosIWA]

Thanks so much.  This seems to be doing the trick.  I am validating the  numbers now. I have 2 more follow up questions if you don't mind... 

1. For my learning purposes since I am a beginner.  What is the purpose of the 'Count Final Data' measure { Count Final Data = COUNTROWS('Final Data Set') }

2. What forumula (i'm assuming another measure) could I do to show the count as a percent of each.   For example 45% of Marta's account have no email.  30% of Marta's accounts have no phone number. 

Thanks again for your help. 

Panos

[PanosIWA]

Sorry forgot to add attachment...  Here is what I have now.

[Anonymous · Accepted solution]

The purpose of "Count Final Data" is to set up the measure that does the work itself. The other 3 measures simply call the measure and apply the additional constraints.

To get a percentage try something like:

Count PBI Email Pct = var denominator = [Count Final Data]
var numerator = CALCULATE(
	[Count Final Data],
	'Final Data Set'[PBI Email] = 0
)
RETURN
DIVIDE(numerator, denominator)