Forum Discussion
Helper ICount Multiple Cell Values and Without Counting Blank Cell
Please i need help i counting all the value in the cell fileds as unique count without counting the blank cell, and have the total unique count sum up. As shown in the Pictures, please what measures can i use?
Hi nosirui54
If you need a column use this code:
Column = CALCULATE ( COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] ) + COUNT ( 'Table'[Number] ) + COUNT ( 'Table'[FIPS] ) + COUNTA ( 'Table'[Postal_Code] ) + COUNT ( 'Table'[State] ) + COUNT ( 'Table'[County] ), FILTER ( 'Table', 'Table'[Months] = EARLIER ( 'Table'[Months] ) ) )Output:
If you need a measure:
Measure = CALCULATE ( COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] ) + COUNT ( 'Table'[Number] ) + COUNT ( 'Table'[FIPS] ) + COUNTA ( 'Table'[Postal_Code] ) + COUNT ( 'Table'[State] ) + COUNT ( 'Table'[County] ), FILTER ( 'Table', 'Table'[Months] = max ( 'Table'[Months] ) ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
6 Replies
- VahidDM
Super User
Hi nosirui54
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
nosirui54Thank you for your response. What am trying to achieve is this:
1. I want each Cells with Values to count as one and then show counts total in the Expected_Output_Counts Column2. If the cell is blank it shouldnt count blank cell as value, because is blank
Months Year Number FIPS Postal_Code State County Expected_Output_Counts January 2001 1 1000 36003 Alabama Autauga 7 February 2002 2 1001 36003 Alabama Autauga 7 March 2003 3 1003 31034 Alabama Baldwin 7 April 2004 4 1005 26238 Alabama Barbour 7 May 2005 5 1007 Texas Valley 6 June 2006 6 1009 4 July 2007 7 1011 4 August 2007 8 1011 Texas 5 - VahidDM
Hi nosirui54
If you need a column use this code:
Column = CALCULATE ( COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] ) + COUNT ( 'Table'[Number] ) + COUNT ( 'Table'[FIPS] ) + COUNTA ( 'Table'[Postal_Code] ) + COUNT ( 'Table'[State] ) + COUNT ( 'Table'[County] ), FILTER ( 'Table', 'Table'[Months] = EARLIER ( 'Table'[Months] ) ) )Output:
If you need a measure:
Measure = CALCULATE ( COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] ) + COUNT ( 'Table'[Number] ) + COUNT ( 'Table'[FIPS] ) + COUNTA ( 'Table'[Postal_Code] ) + COUNT ( 'Table'[State] ) + COUNT ( 'Table'[County] ), FILTER ( 'Table', 'Table'[Months] = max ( 'Table'[Months] ) ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/