Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nosirui54
Helper I
Helper I

Count 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?

Capture_Help.JPG

 

1 ACCEPTED SOLUTION

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:

VahidDM_0-1636595865621.png

 

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:

VahidDM_1-1636595968985.png

 

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/

 

 

 

 

View solution in original post

6 REPLIES 6
VahidDM
Super User
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/

Thank 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 Column

2. If the cell is blank it shouldnt count blank cell as value, because is blank

MonthsYearNumberFIPSPostal_CodeStateCountyExpected_Output_Counts
January20011100036003AlabamaAutauga7
February20022100136003AlabamaAutauga7
March20033100331034AlabamaBaldwin7
April20044100526238AlabamaBarbour7
May200551007 TexasValley6
June200661009   4
July200771011   4
August200781011 Texas 5

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:

VahidDM_0-1636595865621.png

 

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:

VahidDM_1-1636595968985.png

 

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/

 

 

 

 

Its Work. Thank you SO much for the Solutions. 
Please is there a way to avoid the Filters? because some of the Columns i will be filtreing with does not have Value. 

No Worries.

 

Can you please tell me more or share a sample re that Filter issue?

 


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Thank you for your time. What am trying to say is that what if the colomn does not have Calendar and am trying to achieve the same result as shown below: Thank you

NumberFIPSPostal_CodeStateCountyExpected_Output_Counts
1100036003AlabamaAutauga5
2100136003AlabamaAutauga5
3 31034AlabamaBaldwin4
 100526238AlabamaBarbour4
51007 TexasValley4
6  IndinanaMarion3
71011   2
 1011 Texas 2

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.