Forum Discussion
Counting Rows with Zero Values
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)
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.
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.
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.
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
Sorry forgot to add attachment... Here is what I have now.
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!
